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

Lonni J Friedman netllama at gmail.com
Wed Jun 6 00:46:48 JST 2012


On Mon, Jun 4, 2012 at 10:21 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>> 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?

hundreds.  I don't see any option for setting the relcache size.  How
do I make this change?

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

ok.


More information about the pgpool-general mailing list