Re: [Postgres-xc-general] my first results with pgxc 1.1

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

This is a quick comment to your configuration.

1. It's better to configure gtm_proxy for each VM.  gtm_proxy groups
request/response between coordinator/datanode and gtm to reduce the network
workload.

2. You may not need this many max_connection of each datanode.   Your max
connection to each coordinator is 200 so max connection to each datanode
will be 200 x 4 = 800.   Just in case, I suppose 1000 is sufficient.

3. Unfortunately, pgbench is not fullly tuned for XC.   If you have
specific application in mind, you may want to run it.   Or, you can try
DBT-1 benchmark, which is better tuned for XC, installation will be a bit
complicated though.   It is available from Postgres-XC development page,
https://sourceforge.net/projects/postgres-xc/  You will find dbt-1 repo at
git tag of the page.

Good luck.

---
Koichi Suzuki

2013/10/2 Hector M. Jacas <hec...@et...>

>
> Dear Sir,
>
> Below I discuss the first results of the tests performed with version 1.1
> of pgxc official.
>
> During the rest of the week I will be making adjustments to the settings
> in order to get better performance and up to 300 concurrent connections
> (now I can only reach up to 150 concurrent connections).
>
> If someone with experience in this solution can provide it will be welcome.
>
> The scenario I have is as follows:
>
> In a cluster VMware ESXi 5.1 Update 1 with 3 esx host HPBL685c G7 427.9 GB
> of ram one vApp with 4 virtual machines.
>
> 3 of these virtual machines with 16 vCPU and 1GB of ram per vCPU. Each
> virtual machine acts of gtm_proxy / coordinator / data node.
>
> The fourth and last virtual machine (4 vCPUs and 1GB of ram per vCPU) acts
> of GTM.
>
> Operating System: RHELv6.4 (Kernel 2.6.32-358.18.1.el6.x86_64)
> Installation Type: Basic
> Additional Packages: Development Kit
> Filesystem type for the data area: XFS without additional adjustments
>
> This cluster of postgres-xc is balanced with pirahna (LVS) with a virtual
> IP (192.168.97.44) and port 5432 using the LC method (less connections)
>
> dn01/192.168.97.40:(gtm_proxy p:6666/coordinator p:5432/DataNode p:5433)
> dn02/192.168.97.41:(gtm_proxy p:6666/coordinator p:5432/DataNode p:5433)
> dn03/192.168.97.42:(gtm_proxy p:6666/coordinator p:5432/DataNode p:5433)
> gtm /192.168.97.43: (gtm p:6666)
>
> The deployment method used is pgxc_ctl with some additional configuration
> parameters through coordExtraConfig and datanodeExtraConfig and whose
> contents will describe:
>
> coordExtraConfig:
>
> # ==============================**==================
> # Added to all the coordinator postgresql.conf
> # Original: coordExtraConfig
> log_destination = 'syslog'
> logging_collector = on
> log_directory = 'pg_log'
> listen_addresses = '*'
> max_connections = 200
>
> datanodeExtraConfig:
> # ==============================**==================
> # Added to all the coordinator postgresql.conf
> # Original: datanodeExtraConfig
> log_destination = 'syslog'
> logging_collector = on
> log_directory = 'pg_log'
> listen_addresses = '*'
> max_connections = 4096
> fsync = off
> shared_buffers = 4GB
> wal_buffers = 1MB
> checkpoint_timeout = 5min
> checkpoint_segments = 16
> maintenance_work_mem = 4GB
> max_prepared_transactions = 4096
> synchronous_commit = off
>
> NOTE: I should point out that in the case of gtm_proxy definition, I was
> unable to add through gtmPxyExtraConfig parameter, additional parameters
> such as worker_threads = 15.
>
> In this particular case, invariably reflected in the final configuration
> worker_threads = 1
>
> Ok, the results:
>
> [root@rhelclient ~]# createdb -h 192.168.97.44 -U postgres pgbench
> [root@rhelclient ~]# pgbench -i -s 100 -h 192.168.97.44 -U postgres
> pgbench
> NOTICE:  table "pgbench_branches" does not exist, skipping
> NOTICE:  table "pgbench_tellers" does not exist, skipping
> NOTICE:  table "pgbench_accounts" does not exist, skipping
> NOTICE:  table "pgbench_history" does not exist, skipping
> creating tables...
> 10000 tuples done.
> 20000 tuples done.
> 30000 tuples done.
> 40000 tuples done.
> :
> :
> :
> 9950000 tuples done.
> 9960000 tuples done.
> 9970000 tuples done.
> 9980000 tuples done.
> 9990000 tuples done.
> 10000000 tuples done.
> set primary key...
> NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
> "pgbench_branches_pkey" for table "pgbench_branches"
> NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
> "pgbench_tellers_pkey" for table "pgbench_tellers"
> NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
> "pgbench_accounts_pkey" for table "pgbench_accounts"
> vacuum...done.
>
> [root@rhelclient ~]# pgbench -c 16 -j 8 -T 60 -h 192.168.97.44 -U
> postgres pgbench
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 100
> query mode: simple
> number of clients: 16
> number of threads: 8
> duration: 60 s
> number of transactions actually processed: 10217
> tps = 170.186523 (including connections establishing)
> tps = 170.279171 (excluding connections establishing)
>
> [root@rhelclient ~]# pgbench -S -c 16 -j 8 -T 60 -h 192.168.97.44 -U
> postgres pgbench
> starting vacuum...end.
> transaction type: SELECT only
> scaling factor: 100
> query mode: simple
> number of clients: 16
> number of threads: 8
> duration: 60 s
> number of transactions actually processed: 62484
> tps = 1041.168271 (including connections establishing)
> tps = 1041.592065 (excluding connections establishing)
>
> [root@rhelclient ~]# pgbench -c 16 -j 16 -T 60 -h 192.168.97.44 -U
> postgres pgbench
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 100
> query mode: simple
> number of clients: 16
> number of threads: 16
> duration: 60 s
> number of transactions actually processed: 14001
> tps = 232.131191 (including connections establishing)
> tps = 232.374844 (excluding connections establishing)
>
> [root@rhelclient ~]# pgbench -S -c 16 -j 16 -T 60 -h 192.168.97.44 -U
> postgres pgbench
> starting vacuum...end.
> transaction type: SELECT only
> scaling factor: 100
> query mode: simple
> number of clients: 16
> number of threads: 16
> duration: 60 s
> number of transactions actually processed: 59981
> tps = 998.525044 (including connections establishing)
> tps = 998.773480 (excluding connections establishing)
>
> [root@rhelclient ~]# pgbench -c 96 -j 8 -T 60 -h 192.168.97.44 -U
> postgres pgbench
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 100
> query mode: simple
> number of clients: 96
> number of threads: 8
> duration: 60 s
> number of transactions actually processed: 25577
> tps = 423.132610 (including connections establishing)
> tps = 424.137946 (excluding connections establishing)
>
> [root@rhelclient ~]# pgbench -S -c 96 -j 8 -T 60 -h 192.168.97.44 -U
> postgres pgbench
> starting vacuum...end.
> transaction type: SELECT only
> scaling factor: 100
> query mode: simple
> number of clients: 96
> number of threads: 8
> duration: 60 s
> number of transactions actually processed: 115994
> tps = 1929.694756 (including connections establishing)
> tps = 1939.253901 (excluding connections establishing)
>
> [root@rhelclient ~]# pgbench -c 64 -j 32 -T 60 -h 192.168.97.44 -U
> postgres pgbench
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 100
> query mode: simple
> number of clients: 64
> number of threads: 32
> duration: 60 s
> number of transactions actually processed: 14087
> tps = 234.278526 (including connections establishing)
> tps = 234.472208 (excluding connections establishing)
>
> [root@rhelclient ~]# pgbench -S -c 64 -j 32 -T 60 -h 192.168.97.44 -U
> postgres pgbench
> starting vacuum...end.
> transaction type: SELECT only
> scaling factor: 100
> query mode: simple
> number of clients: 64
> number of threads: 32
> duration: 60 s
> number of transactions actually processed: 124367
> tps = 2072.136619 (including connections establishing)
> tps = 2073.475197 (excluding connections establishing)
>
> [root@rhelclient ~]# pgbench -c 64 -j 64 -T 60 -h 192.168.97.44 -U
> postgres pgbench
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 100
> query mode: simple
> number of clients: 64
> number of threads: 64
> duration: 60 s
> number of transactions actually processed: 14239
> tps = 232.935903 (including connections establishing)
> tps = 236.194708 (excluding connections establishing)
>
> [root@rhelclient ~]# pgbench -S -c 64 -j 64 -T 60 -h 192.168.97.44 -U
> postgres pgbench
> starting vacuum...end.
> transaction type: SELECT only
> scaling factor: 100
> query mode: simple
> number of clients: 64
> number of threads: 64
> duration: 60 s
> number of transactions actually processed: 95342
> tps = 1530.270930 (including connections establishing)
> tps = 1531.320634 (excluding connections establishing)
>
> [root@rhelclient ~]# pgbench -c 104 -j 8 -T 60 -h 192.168.97.44 -U
> postgres pgbench
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 100
> query mode: simple
> number of clients: 104
> number of threads: 8
> duration: 60 s
> number of transactions actually processed: 33543
> tps = 542.183967 (including connections establishing)
> tps = 543.542901 (excluding connections establishing)
>
> [root@rhelclient ~]# pgbench -S -c 104 -j 8 -T 60 -h 192.168.97.44 -U
> postgres pgbench
> starting vacuum...end.
> transaction type: SELECT only
> scaling factor: 100
> query mode: simple
> number of clients: 104
> number of threads: 8
> duration: 60 s
> number of transactions actually processed: 286291
> tps = 4770.397054 (including connections establishing)
> tps = 4790.285324 (excluding connections establishing)
> [root@rhelclient ~]#
> ---
> This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE
> running at host imx3.etecsa.cu
> Visit our web-site: <http://www.kaspersky.com>, <http://www.viruslist.com>
>
>
> ------------------------------------------------------------------------------
> October Webinars: Code for Performance
> Free Intel webinars can help you accelerate application performance.
> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most
> from
> the latest Intel processors and coprocessors. See abstracts and register >
> http://pubads.g.doubleclick.net/gampad/clk?id=60134791&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>
>