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

[Lonni J Friedman]

On Thu, May 31, 2012 at 3:54 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>> On Tue, May 29, 2012 at 6:34 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>>> On Tue, May 29, 2012 at 5:32 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>>>>> On Tue, May 29, 2012 at 3:47 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>>>>>>> On 05/29/2012 07:40 AM, Lonni J Friedman wrote:
>>>> How would I determine how often the catalog access happens?
>>>
>>> Enable query log on the master with %r = remote host and port in the
>>> log_line_prefix. This will show any query comes from pgpool (I assume
>>> you are running pgpool on a decicated server and nobody access master
>>> directrly on the server).
>>
>> This is going to require a restart of the master unless I've
>> misunderstood.  I'm not going to be able to do that for quite some
>> time.  I've only had 1 scheduled outage in the past 6 months (and that
>> was ironically 10 days ago).  I likely won't have another until the
>> end of this year, at the earliest.
>>
>> Is there some way to (re)configure pgpool to capture the frequency of
>> the catalog queries?
>
> I think changing log_line_prefix only requires pg_ctl reload.

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.