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

[Tatsuo Ishii]

"-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
>>