[pgpool-general: 3429] Re: Performance degradation with load balancing

[Keith Fiske]

Still confused by these results.

I turned connection caching back on in pgpool and used the -C option to
pgbench with the following result. tps seems on par with just doing a
direct connection to the master, but only if the connection establishment
is excluded. Included, it's pretty horrible.

kfiske at pgpool-base:/etc/pgpool2$ pgbench -c 20 -j 10 -T 300 -S -C 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: 2217927
latency average: 2.705 ms
tps = 7393.042019 (including connections establishing)
tps = 119967.718643 (excluding connections establishing)

Running again without -C and back to the performance being halved

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: 15227370
latency average: 0.394 ms
tps = 50757.799330 (including connections establishing)
tps = 50760.275039 (excluding connections establishing)



--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com

On Mon, Jan 19, 2015 at 6:05 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:

> If you want to test the effect of connection caching, you should turn
> on -C option of pgbench.
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>
> > 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/20150120/be3bbecb/attachment.html>