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

[Lonni J Friedman]

On Mon, Jun 11, 2012 at 4:13 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>> On Thu, Jun 7, 2012 at 11:01 AM, Lonni J Friedman <netllama at gmail.com> wrote:
>>> On Tue, Jun 5, 2012 at 6:06 PM, Lonni J Friedman <netllama at gmail.com> wrote:
>>>> On Tue, Jun 5, 2012 at 5:26 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>>>>> On Tue, Jun 5, 2012 at 4:13 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>>>>>>>> On Mon, Jun 4, 2012 at 10:21 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>>>>>>>>> On Mon, Jun 4, 2012 at 3:40 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>>>>>>>>>>>> You're correct, I only needed to perform a reload.  I kept this change
>>>>>>>>>>>> enabled for all of 36 seconds, and in that time there were 1597 times
>>>>>>>>>>>> that the following query was logged originating from the pgpool server
>>>>>>>>>>>> IP address:
>>>>>>>>>>>> SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid ...
>>>>>>>>>>>>
>>>>>>>>>>>> 3204 times that the following query was logged originating from the
>>>>>>>>>>>> pgpool server IP address:
>>>>>>>>>>>> SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.relname ...
>>>>>>>>>>>>
>>>>>>>>>>>> Those numbers seem excessive to me, but perhaps this is
>>>>>>>>>>>> expected/normal?  It definitely seems like it would explain why perf
>>>>>>>>>>>> always degrades whenever the master is very busy, if that volume of
>>>>>>>>>>>> queries needs to be sustained.
>>>>>>>>>>>
>>>>>>>>>>> That depends on your use case. If client's query involves many tables,
>>>>>>>>>>> it requires many catalog lookups. Also the catalog cache of pgpool's
>>>>>>>>>>> life time is same as pgpool child process lifetime.  If you would show
>>>>>>>>>>> me the complete log, I could make more precise analysis.
>>>>>>>>>>
>>>>>>>>>> Sure, attached as pg.log.gz.
>>>>>>>>>
>>>>>>>>> Analyzing the log I noticed several things:
>>>>>>>>>
>>>>>>>>> 1) I see many sessions which frequently connects/disconnects(over 1800
>>>>>>>>>   in 36 seconds). That is, each session only lasts 20 ms in average.
>>>>>>>>>
>>>>>>>>> 2) A process issues exactly same query more than once. This suggests
>>>>>>>>>   that relcache (catalog cache) size might be too small for your
>>>>>>>>>   environment. The size of the cache is vary from cache to cache, but
>>>>>>>>>   typical size is 128. So if you access more than 128 tables via
>>>>>>>>>   pgpool, the cache replacement will happen. How many tables do you
>>>>>>>>>   have?
>>>>>>>>
>>>>>>>> hundreds.  I don't see any option for setting the relcache size.  How
>>>>>>>> do I make this change?
>>>>>>>
>>>>>>> Currently the only way is changing the source. pgpool-II 3.2 will has
>>>>>>> new directive(relcache_size) to control this.
>>>>>>
>>>>>> When do you anticipate that 3.2 will be released?
>>>>>
>>>>> Officially not decided yet. I personally expect 3.2 is going to be out
>>>>> by the end of this month.
>>>>>
>>>>>> Where in the source of 3.1.x would I make this change, and what would
>>>>>> you suggest I change it to?
>>>>>
>>>>> grep "pool_create_relcache(" *.c will show places where relcache is
>>>>> created. pool_relcache's first argument is the number of cache
>>>>> entries, which you would want to increase.
>>>
>>> I don't have a good understanding of what the number represents, or
>>> how much I'd ideally need.   what would you suggest increasing the
>>> value to, or how can I determine a good value?
>>
>> I can't tell if the lack of a reply is because you don't have time to
>> answer, or for some other reason.  I didn't think my questions were
>> unreasonable.  I'd really appreciate some guidance on how to address
>> this, as its a serious issue in my environment.
>
> You should increase them until cache replacement does not
> happen. Problem is, how to know cache replacement? (Pgpool-II 3.2 will
> emit log in this case)

OK, if that's the case, then I guess I'm stuck until 3.2 is available.
 Please make sure that whatever message appears in the log is well
documented, along with clear guidance on how to select a larger cache
size, so that I know what to look for.  Or alternatively, what would
be the impact of selecting a value that was too large?  Performance,
stability, something else?

>
> So my only suggestion is, increase to number of tables you have in
> your database.

Sorry, I'm not following this, unless you really meant "decrease"
instead of "increase".  How would increasing the number of tables in a
database help?  I thought the problem was a result of there being too
many tables to fit into the hardcoded cache  size limit?  Also, am I
supposed to just randomly create tables that serve no purpose?