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

Tatsuo Ishii ishii at postgresql.org
Tue May 22 09:24:45 JST 2012

> 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

More information about the pgpool-general mailing list