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

[Keith Fiske]

I think the -C option was my issue. You're right, that is a more realistic
method that will be being used and I'm seeing things act more consistently
now.

With pgpool:

kfiske at pgpool-base:~$ pgbench -c 20 -j 10 -T 300 -C -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: 2217400
latency average: 2.706 ms
tps = 7391.287680 (including connections establishing)
tps = 115036.308523 (excluding connections establishing)

Without pgpool (connecting directly to master from pgpool system)

kfiske at pgpool-base:~$ pgbench -h db01 -c 20 -j 10 -T 300 -C -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: 113890
latency average: 52.682 ms
tps = 373.709111 (including connections establishing)
tps = 17966.912516 (excluding connections establishing)

Arigatou gozaimasu :)


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

On Tue, Jan 20, 2015 at 5:59 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:

> "-C" simulates more real world like environment: client needs to
> connect to DB server every time starting a session. So performance
> dropping is expected. Also you need to run pgbench with -C against
> PostgreSQL to get an apple-t-apple comparison. which I expect huge
> performance dropping because the overhead to start a session in
> PostgreSQL is pretty high. One of the reason why pgpool was born is to
> solve the problem.
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>
> > 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/20150121/22316184/attachment.html>