[pgpool-general: 3426] Performance degradation with load balancing

[Keith Fiske]

Evaluating pgpool for a client as a load balancer and running into some
unexpected performance degradation. Using pg_bench, read-only selects are
going half as fast through pgpool as they are going directly to the master
system. Does not seem to be a network issue between the pgpool server and
the databases since connecting directly to either the master or the slave
from the pgpool server works fine.

I saw this issue on the list archive
http://www.sraoss.jp/pipermail/pgpool-general/2015-January/003438.html
but I'm not in a position right now to be testing a patched version on
their systems. I tried turning off connection caching and doing the kernel
tuning as mentioned in that email. The first kernel tuning parameter
doesn't seem to exist for this system (Ubuntu 12.04) and changing the other
two didn't seem to make any difference.

If anyone has any other suggestions for what to look at, I'd appreciate it.
The only thing pgpool is being used for is load balancing and connection
pooling. All other options are turned off.

SELECT directly from master from the pgpool server but not using pgpool
kfiske at pgpool-base:/etc/pgpool2$ pgbench -h db01 -c 20 -j 10 -T 300 -S
pgbench
Password:
starting vacuum...end.
transaction type: SELECT only
scaling factor: 1000
query mode: simple
number of clients: 20
number of threads: 10
duration: 300 s
number of transactions actually processed: 33780221
latency average: 0.178 ms
tps = 112600.544120 (including connections establishing)
tps = 112610.109694 (excluding connections establishing)


SELECT directly from slave from the pgpool server but not using pgpool
kfiske at pgpool-base:/etc/pgpool2$ pgbench -h db02 -c 20 -j 10 -T 300 -S
pgbench -n
Password:
transaction type: SELECT only
scaling factor: 1000
query mode: simple
number of clients: 20
number of threads: 10
duration: 300 s
number of transactions actually processed: 30087965
latency average: 0.199 ms
tps = 100293.060878 (including connections establishing)
tps = 100302.790535 (excluding connections establishing)


SELECT only from pgpool (to ensure load-balancing works)
kfiske at pgpool-base:/etc/pgpool2$ pgbench -c 20 -j 10 -T 300 -S pgbench
Password:
starting vacuum...end.
transaction type: SELECT only
scaling factor: 1000
query mode: simple
number of clients: 20
number of threads: 10
duration: 300 s
number of transactions actually processed: 14781236
latency average: 0.406 ms
tps = 49270.616847 (including connections establishing)
tps = 49273.323274 (excluding connections establishing)

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20150119/8288639d/attachment.html>