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

[Lonni J Friedman]

On Mon, May 21, 2012 at 5:24 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>> 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?

I don't have a query log, as we're not logging queries.  I guess I
could enable query logging in pgpool if you think that's going to help
you investigate this further, but I'm concerned that's going to create
significant disk activity on the pgpool server.

But the issue isn't that specific queries are a bottleneck but rather
that if there's a large amount of write activity, the load on the
master will be much greater than on the standby/slaves, thereby
reducing the performance of both the master & the standby/slaves.