[pgpool-general: 256] Re: Slow Pgpool performance on load balancing

[Tatsuo Ishii]

pgbench -S is not the best test case for pgpool because:

1) the SELECT is too trivial thus the overhead of pgpool is too high
   lighted. It's better more realistic SELECT use case from your own
   application.

2) the database size is too small. You want to try bigger scale factor.

BTW, on my Linux laptop with CORE i5 4GB mem with SSD, I see following
number. Pgpool-II and primary/standby PostgreSQl are all on the box.

$ pgbench -S -C -t 20 -c 20 test (via pgpool)
starting vacuum...end.
transaction type: SELECT only
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 20
number of transactions actually processed: 400/400
tps = 588.130641 (including connections establishing)
tps = 20086.371397 (excluding connections establishing)
$ pgbench -p 5433 -S -C -t 20 -c 20 test (directly connected to PostgreSQL)
starting vacuum...end.
transaction type: SELECT only
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 20
number of transactions actually processed: 400/400
tps = 596.715677 (including connections establishing)
tps = 16670.139612 (excluding connections establishing)
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Hi ,
> 
> I am seeing slow performance when do pgbench test for Selects  via pgpoolII
> compared to performing same test over the backend .
> 
> The setup is following .
> 
> 1 Master  - 2 Salve  Streaming replication setup .
> 
> PgpoolII resides on Master node .
> 
> All node have exact software versions and hardware .
> 
> Postgres version  (9.1.3)
> pgpoolII version  3.2alpha1  ( loadbalancing enabled )
> 
> 
> These are pgbench test , there is a huge difference with and without
> pgpoolII . Any idea whats the cause ? Shouldnt the select test with pgpool
> be able to handle more transactions than single backend ?
> 
> *Test 1 :*
> *$ pgbench -p9999 -T 30 -S -n -c 12 bench_replication*
> *transaction type: SELECT only*
> *scaling factor: 1*
> *query mode: simple*
> *number of clients: 12*
> *duration: 30 s*
> *number of transactions actually processed: 562902*
> *tps = 18762.431859 (including connections establishing)*
> *tps = 18791.333944 (excluding connections establishing)*
> *
> *
> *$ pgbench -p5432 -h 10.52.6.142 -T 30 -S -n -c 12 bench_replication*
> *transaction type: SELECT only*
> *scaling factor: 1*
> *query mode: simple*
> *number of clients: 12*
> *duration: 30 s*
> *number of transactions actually processed: 1133075*
> *tps = 37767.193960 (including connections establishing)*
> *tps = 37800.532288 (excluding connections establishing)*
> 
> *Test 2: -C*
> *$ pgbench -p5432 -h 10.52.6.142 -t 20 -S -n -C -c 10 bench_replication*
> *transaction type: SELECT only*
> *scaling factor: 1*
> *query mode: simple*
> *number of clients: 10*
> *number of transactions per client: 20*
> *number of transactions actually processed: 200/200*
> *tps = 386.054924 (including connections establishing)*
> *tps = 28364.770954 (excluding connections establishing)*
> 
> *$ pgbench -p9999  -t 20 -S -n -C -c 10 bench_replication*
> *transaction type: SELECT only*
> *scaling factor: 1*
> *query mode: simple*
> *number of clients: 10*
> *number of transactions per client: 20*
> *number of transactions actually processed: 200/200*
> *tps = 303.073927 (including connections establishing)*
> *tps = 3775.508278 (excluding connections establishing)*
> 
> -- 
> Syed Atif Ali
> Desk: 971 4 4493131