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

[Scott Rankin]

This is sort of in the same vein as Keith’s question below.  I’ve got a setup where I have a master in one datacenter and a slave in another (across the country).  I want to use pgpool in load balancing mode to send read queries to the slave, and writes to the master.  However, I’m seeing a situation where, in load balancing mode, pgpool is making a lot of queries to the master before it makes the read query on the slave:

2015-01-26 21:22:05: pid 182: LOG:  DB node id: 0 backend pid: 19437 statement: SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.oid = pgpool_regclass('drivers') AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog'
2015-01-26 21:22:05: pid 182: LOG:  DB node id: 0 backend pid: 19437 statement: SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.oid = pgpool_regclass('driver_groups') AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog'
2015-01-26 21:22:05: pid 182: LOG:  DB node id: 0 backend pid: 19437 statement: SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.oid = pgpool_regclass('users') AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog'
2015-01-26 21:22:05: pid 182: LOG:  DB node id: 0 backend pid: 19437 statement: SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.oid = pgpool_regclass('companies') AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog'
2015-01-26 21:22:05: pid 182: LOG:  DB node id: 1 backend pid: 62278 statement: Parse: select …..

This is killing my performance. I’ve turned off check_temp_table and check_unlogged_table, but these multiple selects against the master persist.  Is there any way to turn them off completely or perhaps cache the result?

From: Keith Fiske
Date: Wednesday, January 21, 2015 at 1:55 PM
To: Tatsuo Ishii
Cc: "pgpool-general at pgpool.net<mailto:pgpool-general at pgpool.net>"
Subject: [pgpool-general: 3435] Re: Performance degradation with load balancing

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<mailto: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<mailto: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
>>


This email message contains information that Motus, LLC considers confidential and/or proprietary, or may later designate as confidential and proprietary. It is intended only for use of the individual or entity named above and should not be forwarded to any other persons or entities without the express consent of Motus, LLC, nor should it be used for any purpose other than in the course of any potential or actual business relationship with Motus, LLC. If the reader of this message is not the intended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify sender immediately and destroy the original message.

Internal Revenue Service regulations require that certain types of written advice include a disclaimer. To the extent the preceding message contains advice relating to a Federal tax issue, unless expressly stated otherwise the advice is not intended or written to be used, and it cannot be used by the recipient or any other taxpayer, for the purpose of avoiding Federal tax penalties, and was not written to support the promotion or marketing of any transaction or matter discussed herein.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20150126/f705e408/attachment-0001.html>