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

[Lonni J Friedman]

On Tue, Jun 5, 2012 at 4:13 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>> 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?
>
> Currently the only way is changing the source. pgpool-II 3.2 will has
> new directive(relcache_size) to control this.

When do you anticipate that 3.2 will be released?
Where in the source of 3.1.x would I make this change, and what would
you suggest I change it to?