<div dir="ltr"><div><div><div>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.<br><br></div>I saw this issue on the list archive<br><a href="http://www.sraoss.jp/pipermail/pgpool-general/2015-January/003438.html">http://www.sraoss.jp/pipermail/pgpool-general/2015-January/003438.html</a><br></div>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.<br><br></div>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.<br><div><div><div><br>SELECT directly from master from the pgpool server but not using pgpool<br>kfiske@pgpool-base:/etc/pgpool2$ pgbench -h db01 -c 20 -j 10 -T 300 -S pgbench<br>Password: <br>starting vacuum...end.<br>transaction type: SELECT only<br>scaling factor: 1000<br>query mode: simple<br>number of clients: 20<br>number of threads: 10<br>duration: 300 s<br>number of transactions actually processed: 33780221<br>latency average: 0.178 ms<br>tps = 112600.544120 (including connections establishing)<br>tps = 112610.109694 (excluding connections establishing)<br><br><br>SELECT directly from slave from the pgpool server but not using pgpool<br>kfiske@pgpool-base:/etc/pgpool2$ pgbench -h db02 -c 20 -j 10 -T 300 -S pgbench -n<br>Password: <br>transaction type: SELECT only<br>scaling factor: 1000<br>query mode: simple<br>number of clients: 20<br>number of threads: 10<br>duration: 300 s<br>number of transactions actually processed: 30087965<br>latency average: 0.199 ms<br>tps = 100293.060878 (including connections establishing)<br>tps = 100302.790535 (excluding connections establishing)<br><br><br>SELECT only from pgpool (to ensure load-balancing works)<br>kfiske@pgpool-base:/etc/pgpool2$ pgbench -c 20 -j 10 -T 300 -S pgbench<br>Password: <br>starting vacuum...end.<br>transaction type: SELECT only<br>scaling factor: 1000<br>query mode: simple<br>number of clients: 20<br>number of threads: 10<br>duration: 300 s<br>number of transactions actually processed: 14781236<br>latency average: 0.406 ms<br>tps = 49270.616847 (including connections establishing)<br>tps = 49273.323274 (excluding connections establishing)<br><br clear="all"><div><div><div class="gmail_signature">--<br>Keith Fiske<br>Database Administrator<br>OmniTI Computer Consulting, Inc.<br><a href="http://www.keithf4.com" target="_blank">http://www.keithf4.com</a></div></div>
</div></div></div></div></div>