[pgpool-general: 4673] Performance issue with pgpool

[Alexandre Ellert]

Hi,

I have a 2 nodes cluster pgpool 3.5.2 / postgres 9.5 setup with
streaming replication and watchdog (I followed this doc :
http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_master_slave/en.html).

I did some performance tests with pgbench and it seems that pgpool has
a big overhead compared to direct Postgres connection.

Here is some results :

- Connection to pgpool (VIP address), load balance is on and node 1
weight is 0.8 and node 2 is 0.2

root at webtest-database:~# pgbench -U postgres -h
postgres-prod-vip.ivscloud.local -p 9999 -c 4 -j 2 -T 120 -C -S
pgbench
starting vacuum...end.
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 4
number of threads: 2
duration: 120 s
number of transactions actually processed: 5344
latency average: 89.820 ms
tps = 44.514727 (including connections establishing)
tps = 27742.663950 (excluding connections establishing)


- Connection to my first node directly :

root at webtest-database:~# pgbench -U postgres -h
postgres-prod-1.ivscloud.local -p 5432 -c 4 -j 2 -T 120 -C -S pgbench
starting vacuum...end.
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 4
number of threads: 2
duration: 120 s
number of transactions actually processed: 8610
latency average: 55.749 ms
tps = 71.737824 (including connections establishing)
tps = 23750.348256 (excluding connections establishing)


- Connection to my second node directly (hardware is slighly less
performant compared to the fisrt node) :

root at webtest-database:~# pgbench -U postgres -h
postgres-prod-2.ivscloud.local -p 5432 -c 4 -j 2 -T 120 -C -S pgbench
starting vacuum...ERROR:  cannot execute VACUUM during recovery

root at webtest-database:~# pgbench -U postgres -h
postgres-prod-2.ivscloud.local -p 5432 -n -c 4 -j 2 -T 120 -C -S
pgbench
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 4
number of threads: 2
duration: 120 s
number of transactions actually processed: 7185
latency average: 66.806 ms
tps = 59.858654 (including connections establishing)
tps = 22320.180425 (excluding connections establishing)

I you need more information, just tell me.
Thank you for your help.

Alexandre