[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

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 ~]#