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

Tatsuo Ishii ishii at postgresql.org
Wed Jun 6 08:16:47 JST 2012


"128" is number of cache entries.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> What means "The size of the cache is vary from cache to cache, but typical size is 128" the size is in bytes? 
> 
> Regards. 
> 
> ----- Mensaje original -----
> 
>> > 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?
> 
>> 2) The are many queries to check whether the table is a temporary one
>> or not(1589 times). Even if we increase the relcache size, we
>> cannot avoid this since the cache's life time is session. So it
>> might be worth to invent new swich to skip the check.
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general
> 
>> 10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS
>> INFORMATICAS...
>> CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION
> 
>> http://www.uci.cu
>> http://www.facebook.com/universidad.uci
>> http://www.flickr.com/photos/universidad_uci
> 
> 
> 10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS INFORMATICAS...
> CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION
> 
> http://www.uci.cu
> http://www.facebook.com/universidad.uci
> http://www.flickr.com/photos/universidad_uci


More information about the pgpool-general mailing list