[Pgpool-general] pgpool slow in pgbench test

[Tatsuo Ishii]

> Hi,
> 
> I'm testing pgpool with load balancer and replication and it seems to be ok except the speed of SELECT queries.
> 
> The system is a test platform consist in 2 servers with pgpool and postgres installed in main one and a second one with only postgres.
> 
> Please, look at this test.
> 
> To backend0 directly:
> 
> # pgbench -S -c100 -t100 -h192.168.100.52 -p4002 -Upostgres t1
> starting vacuum...end.
> transaction type: SELECT only
> scaling factor: 1
> number of clients: 100
> number of transactions per client: 100
> number of transactions actually processed: 10000/10000
> tps = 9788.231609 (including connections establishing)
> tps = 15270.113029 (excluding connections establishing)
> 
> To backend1 directly:
> 
> # pgbench -S -c100 -t100 -h192.168.100.53 -p4003 -Upostgres t1
> starting vacuum...end.
> transaction type: SELECT only
> scaling factor: 1
> number of clients: 100
> number of transactions per client: 100
> number of transactions actually processed: 10000/10000
> tps = 9592.298535 (including connections establishing)
> tps = 14526.712445 (excluding connections establishing)
> 
> Using pgpool :
> 
> # pgbench -S -c100 -t100 -h192.168.100.52 -p5432 -Upostgres t1
> starting vacuum...end.
> transaction type: SELECT only
> scaling factor: 1
> number of clients: 100
> number of transactions per client: 100
> number of transactions actually processed: 10000/10000
> tps = 6299.831668 (including connections establishing)
> tps = 10573.026307 (excluding connections establishing)
> 
> Some config lines from pgpool.conf :
> 
> port = 5432
> num_init_children = 300
> max_pool = 2
> replication_mode = true
> load_balance_mode = true
> 
> Backend config is :
> 
> backend_hostname0 = 'localhost'
> backend_port0 = 4002
> backend_weight0 = 1
> backend_data_directory0 = '/usr/local/pgpool/data-pp2'
> backend_hostname1 = '192.168.100.53'
> backend_port1 = 4003
> backend_weight1 = 1
> backend_data_directory1 = '/usr/local/pgpool/data-pp3'
> 
> Anyone has experienced similar behavior ?

This is not surprising because:

1) pgbench -S execute really trivial SELECT, which emphasizes the
   overhead of pgpool.

2) pgbench without -C keep the connection to PostgreSQL which hides
   the effect of connection pooling. In real world, DB applications
   does have their own connection pooling (except Java), and that's
   reason why pgpool was born.

So my suggestion is, run pgbench with -C. Or even better, create
custom script for pgpool which includes more real-world SELECTs.

Of course I want to lower the overhead if I could. Patches are always
welcome:-) Or profiling would be usefull.
--
Tatsuo Ishii
SRA OSS, Inc. Japan