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

[Tatsuo Ishii]

> On Mon, May 21, 2012 at 6:13 PM, Lonni J Friedman <netllama at gmail.com> wrote:
>> 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.
> 
> Does the lack of reply mean that I'm basically out of luck on this?
> Personally, I see this behavior as a significant flaw in pgpool's
> design.  Its not true load balancing if every query is gated by the
> performance of 1 server.

Any idea how to solve this?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp