|
From: Hector M. J. <hec...@et...> - 2013-10-01 20:41:58
|
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 ~]# |