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

[Tatsuo Ishii]

> On Mon, May 21, 2012 at 3:57 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>> Greetings,
>>> I'm running pgpool-II-3.1.2 purely for load balancing in front of a 4
>>> node postgresql-9.1.3 cluster (all running on Linux-x86_64).  I'm
>>> using streaming replication with 3 hot standby servers.  I have the
>>> weighting distributed such that the master weight is set to '1' and
>>> the three standby servers are set to '33' each.  My intent was that
>>> this would nearly eliminate all read queries going to the master,
>>> however what I'm observing is that the overall performance seems to be
>>> bottlenecked by the performance of the master, even when queries
>>> aren't ultimately getting sent to the master.
>>>
>>> For example, there are times when the load on the master is much
>>> higher than on the standby servers, and the master's overall
>>> performance is noticeably degraded.  When this occurs, I've found that
>>> establishing a database connection through pgpool with a SQL query
>>> that should normally go to one of the standby servers has very poor
>>> performance.  It seems like pgpool is silently doing something with
>>> the master (which is under load and slower to respond), before passing
>>> the query itself off to a standby server.
>>>
>>> Is this expected behavior?
>>
>> Yes. Pgpool needs to send query to system catalogs on primary to
>> analyze user queries (for example, if the refereed table is a
>> temporary table or not).
> 
> Thanks, that's good to know, but also disappointing.  This behavior is
> serving as a huge bottleneck in my environment.  It basically means
> that no standby server can respond any faster than the master, which
> defeats much of the benefit of load balancing.
> 
> Is there any way to work around this, or mitigate it to some degree?
> For example, I'm not using temp tables for anything in my environment,
> so not performing that check wouldn't have any impact on me.

I thought the access to system catalogs are not big pain because
pgpool has session life time caches for this. However it seems they
might be bottle neck in your case. I would like to know what kind of
quries are major bottle neck in your case. Could you show me the query
log on the primary?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp