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

[Tatsuo Ishii]

> On Tue, May 29, 2012 at 6:34 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>> On Tue, May 29, 2012 at 5:32 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>>>> On Tue, May 29, 2012 at 3:47 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>>>>>> On 05/29/2012 07:40 AM, Lonni J Friedman wrote:
>>>>>>>> On Mon, May 28, 2012 at 10:28 PM, Matt Solnit<msolnit at soasta.com>
>>>>>>>> wrote:
>>>>>>>>> On May 28, 2012, at 6:53 PM, Lonni J Friedman<netllama at gmail.com>
>>>>>>>>> wrote:
>>>>>>>>>> On Mon, May 28, 2012 at 5:23 PM, Tatsuo Ishii<ishii at postgresql.org>
>>>>>>>>>> wrote:
>>>>>>>>>>>> On Mon, May 28, 2012 at 3:54 PM, Tatsuo Ishii<ishii at sraoss.co.jp>
>>>>>>>>>>>> wrote:
>>>>>>>>>>>>>> What are the reasons for analysing system catalogs on primary server?
>>>>>>>>>>>>>
>>>>>>>>>>>>> For example, if a table is a temporary one or not.
>>>>>>>>>>>>
>>>>>>>>>>>> Yes, but as I noted, I don't use temp tables at all. ?If this is the
>>>>>>>>>>>> primary justification, then its not doing me any good, and causing
>>>>>>>>>>>> unnecessary negative performance impact.
>>>>>>>>>>>
>>>>>>>>>>> But how does pgpool know that you are not going to use temporary
>>>>>>>>>>> tables beforehand?
>>>>>>>>>>
>>>>>>>>>> Provide a new pgpool.conf option that tells it to ignore them (with
>>>>>>>>>> the assumption that they do not exist).
>>>>>>>>>
>>>>>>>>> +1 for new pgpool.conf setting, although I think the default should be
>>>>>>>>> the
>>>>>>>>> current behavior, for backward compatibility.
>>>>>>>>>
>>>>>>>>> Maybe something like "enable_temp_tables", although that might be too
>>>>>>>>> vague.  "on" means current behavior, all system catalog queries go to
>>>>>>>>> the
>>>>>>>>> master.  "off" means that system catalog queries are load-balanced
>>>>>>>>> just
>>>>>>>>> like any other.
>>>>>>>>
>>>>>>>> agreed.
>>>>>>>>
>>>>>>>> Another idea is to create a new pgpool.conf option which makes the
>>>>>>>> system catalogue check a configurable interval, as one of the
>>>>>>>> following:
>>>>>>>> * every query (current/default behavior)
>>>>>>>> * when pgpool is started only
>>>>>>>> * once every X minutes or X queries
>>>>>>>>
>>>>>>>> This would provide people with a tradeoff between performance and
>>>>>>>> accuracy.  Anyone who never or rarely makes changes (and/or never uses
>>>>>>>> temp tables) would likely prefer the 2nd or 3rd setting, while those
>>>>>>>> who often make changes and/or use temp tables, would stick with the
>>>>>>>> default (current behavior).
>>>>>>>
>>>>>>> Yeah that's sort of where I was going with it, it's apparently not
>>>>>>> easy to move those checks.
>>>>>>>
>>>>>>> enable_temp_tables=y/n seems like a possible workaround for now, but I
>>>>>>> have to assume that there are people out there that want to use
>>>>>>> temp/unlogged tables and also will be write heavy and want to scale
>>>>>>> out in this manner.
>>>>>>
>>>>>> Before jump into any of these ideas, I would like to confirm if the
>>>>>> those accesses are really a bottle neck or not. Pgpool caches results
>>>>>> of accessing system catalogs and the cache persists as long as the
>>>>>> connection pool remains. So unless your set the life time of
>>>>>> connection pool very short, I guess those system catalog accesses are
>>>>>> not the bottle neck in the real world.
>>>>>
>>>>> My settings are as follows:
>>>>> child_life_time = 300
>>>>> child_max_connections = 2990
>>>>> connection_life_time = 0
>>>>> client_idle_limit = 180
>>>>>
>>>>> and I have the weighting split 33/33/33/1 with 33 going to the read
>>>>> servers, and 1 going to the master.
>>>>>
>>>>> All that I'm certain of is that the responsiveness of the database
>>>>> cluster to any query degrades as the load on the master increases.
>>>>> I've seen numerous cases where the standby/slave servers had a load of
>>>>> less than 1.00, yet I was still waiting seconds to get a response from
>>>>> a trivial query.  I've even had cases where I've manually run the same
>>>>> query on the standby/slave directly, and it came before the query
>>>>> going through pgpool.
>>>>
>>>> This sounds too vage evidence that pgpool's catalog accesess is the
>>>> major bottle neck of your case. I would like to know clearer
>>>> evidence. For example, how many times catalog accesses happen, and
>>>> each access takes non trivial execution time.
>>>
>>> How would I determine how often the catalog access happens?
>>
>> Enable query log on the master with %r = remote host and port in the
>> log_line_prefix. This will show any query comes from pgpool (I assume
>> you are running pgpool on a decicated server and nobody access master
>> directrly on the server).
> 
> This is going to require a restart of the master unless I've
> misunderstood.  I'm not going to be able to do that for quite some
> time.  I've only had 1 scheduled outage in the past 6 months (and that
> was ironically 10 days ago).  I likely won't have another until the
> end of this year, at the earliest.
> 
> Is there some way to (re)configure pgpool to capture the frequency of
> the catalog queries?

I think changing log_line_prefix only requires pg_ctl reload.

>>> Regardless, I'm not who implicated the catalog access as the problem,
>>> it was you who suggested that initially.
>>
>> But you said:
>>> 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.
>>
>> So you are thinking that pgpool's (whatever) access to the master is
>> cause of the performance problem, right?
> 
> Right, as running the same query without going through pgpool was/is faster.
> 
>>
>>>  All I did was describe the
>>> symptoms of the problem.  I'm quite willing to pursue other
>>> possibilities, as long as you can provide guidance on how to do so.
>>>
>>>>
>>>> Or even better, someone comes up with a patch and show the performance
>>>> difference between with/without the patche.
>>>
>>> That someone wouldn't be me, as I'm not remotely qualified to work on
>>> the pgpool codebase.
>>
>> What do you mean by "I'm not remotely qualified to work on the pgpool
>> codebase"?
> 
> You were asking for a patch, and I'm not able to provide one.  I'm not
> a C coder.  Last time I wrote anything in C was back in college, which
> was many years ago.

Apologies if my wording was not appropriate. I'm not asking you
writing a patch. I expected someone who has enough time to try to
write the patch.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp