[pgpool-general: 480] Re: load balancing seems to be bottlenecked by performance of master
Lonni J Friedman
netllama at gmail.com
Tue May 22 08:10:16 JST 2012
On Mon, May 21, 2012 at 3:57 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>> 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.
More information about the pgpool-general