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

[Lonni J Friedman]

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).