[pgpool-general: 592] Re: load balancing seems to be bottlenecked by performance of master

Tatsuo Ishii ishii at postgresql.org
Tue Jun 5 14:21:31 JST 2012


> On Mon, Jun 4, 2012 at 3:40 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>> You're correct, I only needed to perform a reload.  I kept this change
>>> enabled for all of 36 seconds, and in that time there were 1597 times
>>> that the following query was logged originating from the pgpool server
>>> IP address:
>>> SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid ...
>>>
>>> 3204 times that the following query was logged originating from the
>>> pgpool server IP address:
>>> SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.relname ...
>>>
>>> Those numbers seem excessive to me, but perhaps this is
>>> expected/normal?  It definitely seems like it would explain why perf
>>> always degrades whenever the master is very busy, if that volume of
>>> queries needs to be sustained.
>>
>> That depends on your use case. If client's query involves many tables,
>> it requires many catalog lookups. Also the catalog cache of pgpool's
>> life time is same as pgpool child process lifetime.  If you would show
>> me the complete log, I could make more precise analysis.
> 
> Sure, attached as pg.log.gz.

Analyzing the log I noticed several things:

1) I see many sessions which frequently connects/disconnects(over 1800
   in 36 seconds). That is, each session only lasts 20 ms in average.

2) A process issues exactly same query more than once. This suggests
   that relcache (catalog cache) size might be too small for your
   environment. The size of the cache is vary from cache to cache, but
   typical size is 128. So if you access more than 128 tables via
   pgpool, the cache replacement will happen. How many tables do you
   have?

2) The are many queries to check whether the table is a temporary one
   or not(1589 times). Even if we increase the relcache size, we
   cannot avoid this since the cache's life time is session. So it
   might be worth to invent new swich to skip the check.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp


More information about the pgpool-general mailing list