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

Thu Jul 26 04:53:12 JST 2012

I think this value represent the number of relation cached in the pool internal cache.
In the configuration file say that relcache_size is the Number of relation cache entry.

If you install the 3.2 RC1 I recommed you downloading the lastest snapshot of pgpool 3.2 RC1 from git repository because one bug was fixed related with this functionality.

Regards.

________________________________________
From: pgpool-general-bounces at pgpool.net [pgpool-general-bounces at pgpool.net] On Behalf Of Lonni J Friedman [netllama at gmail.com]
Sent: Wednesday, July 25, 2012 2:35 PM
To: Tatsuo Ishii
Cc: pgpool-general at pgpool.net
Subject: [pgpool-general: 810] Re: load balancing seems to be bottlenecked by performance of master

On Mon, Jun 11, 2012 at 5:36 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>> 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.
>
> You will see something like:
>
> LOG: pool_search_relcache: cache replacement happend
>
> in this case.
>
>> Or alternatively, what would
>> be the impact of selecting a value that was too large?  Performance,
>> stability, something else?
>
> Memory size. There are 14 relcaches in the source code. Each relcache
> entry size is 1064 bytes on my 64bit linux. So if you increase the
> relcache size by 10 for example, the memory for a pgpool child will
> increase 10*14*1064 = 148,960 bytes.
>
>>> 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?
>
> Oops. What I meant was:
>
>> So my only suggestion is, increase the number of cache to be larger
>> than the number of tables you have in your database.

I'm looking at the official 3.2-RC1 documentation as it applies to
this (new) functionality, and I think I spotted a bug.  The doc says
"relcache_size V3.2 -    Size of relation cache in seconds. Default is
256. If you see following message frequently, increase the number. ".
Is this really measured in seconds?   256 seems like an odd number of
seconds, plus, I don't usually think of the size of something in
seconds.  Is this an error?

thanks
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net
http://www.pgpool.net/mailman/listinfo/pgpool-general

Fin a la injusticia, LIBERTAD AHORA A NUESTROS CINCO COMPATRIOTAS QUE SE ENCUENTRAN INJUSTAMENTE EN PRISIONES DE LOS EEUU!
http://www.antiterroristas.cu
http://justiciaparaloscinco.wordpress.com