[pgpool-general: 2332] Re: thousands of queries to pg_class / second

[Tatsuo Ishii]

Ok, I found a nasty problem with caching definition for checking
unlogged tables.  There are two kinds of caches: one is "session
global" and "session local". The latter is used for tables whose life
is within a session, such as temp tables. By accident, the unlogged
table cache is created as a session local cache, which was apparently
a wrong decision. Please apply included on-line patch to fix the
problem.

BTW, you seem to forget to install pgpool_regclass function. I
strongly recommend to install it.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Yes 100% sure I am manually pasting out the table name, there's 11 of them
> in less then 1 minute of logs, and that's just for 1 table.
> 
> On Wed, Dec 4, 2013 at 3:32 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
>> Are you sure that those "table" are exactly the same one?
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>>
>> > Ok I scanned the log again and found this:
>> >
>> > 2013-12-04 07:02:36 LOG:   pid 17148: DB node id: 0 backend pid: 6475
>> > statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname
>> =
>> > 'table' AND c.relpersistence = 'u'
>> > 2013-12-04 07:02:46 LOG:   pid 17148: DB node id: 0 backend pid: 6475
>> > statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname
>> =
>> > 'table' AND c.relpersistence = 'u'
>> > 2013-12-04 07:02:49 LOG:   pid 17148: DB node id: 0 backend pid: 6475
>> > statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname
>> =
>> > 'table' AND c.relpersistence = 'u'
>> >
>> > that looks like the same pid not caching.
>> >
>> > On Wed, Dec 4, 2013 at 3:21 PM, Tatsuo Ishii <ishii at postgresql.org>
>> wrote:
>> >
>> >> The cache is per process base, not cluster wide.
>> >>
>> >> That means, unless you find an evidence that same pid process issues
>> >> same catalog access more than once, it can be said that the cache is
>> >> working.
>> >> --
>> >> Tatsuo Ishii
>> >> SRA OSS, Inc. Japan
>> >> English: http://www.sraoss.co.jp/index_en.php
>> >> Japanese: http://www.sraoss.co.jp
>> >>
>> >> > I have just cut out some parts of the log, as it generates mbs of
>> logs a
>> >> > minute as I believe this is what you want, as you can see it doesn't
>> seem
>> >> > to be caching:
>> >> >
>> >> > 2013-12-04 07:02:19 LOG:   pid 17148: DB node id: 0 backend pid: 6475
>> >> > statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE
>> c.relname
>> >> =
>> >> > 'table' AND c.relpersistence = 'u'
>> >> > 2013-12-04 07:02:19 LOG:   pid 17173: DB node id: 0 backend pid: 6463
>> >> > statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE
>> c.relname
>> >> =
>> >> > 'table' AND c.relpersistence = 'u'
>> >> > 2013-12-04 07:02:19 LOG:   pid 17179: DB node id: 0 backend pid: 6462
>> >> > statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE
>> c.relname
>> >> =
>> >> > 'table' AND c.relpersistence = 'u'
>> >> >
>> >> > let me know if this is not what you wanted, we are currently using
>> >> pgpool:
>> >> > 3.3.0 with postgres 9.1
>> >> >
>> >> > *Nathan Brennan* *BCS*
>> >> > Lead Developer
>> >> >
>> >> > *T:* +61 8 9482 3981 | *E:* nathan at healthengine.com.au
>> >> >
>> >> > <http://healthengine.com.au/>      <https://twitter.com/healthengine>
>> >> > <https://www.facebook.com/HealthEngine>
>> >> >
>> >> >
>> >> >
>> >> > On Wed, Dec 4, 2013 at 2:40 PM, Tatsuo Ishii <ishii at postgresql.org>
>> >> wrote:
>> >> >
>> >> >> > ok thanks,
>> >> >> >
>> >> >> > I have disabled the check_temp_table as we don't use temp tables
>> which
>> >> >> > removes the pg_temp queries, however there is still alot of queries
>> >> for
>> >> >> > SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname =
>> >> 'table'
>> >> >> AND
>> >> >> > c.relpersistence = 'u' to me this seems like a check to see if the
>> >> table
>> >> >> > exists, is there a way to cache this ?
>> >> >>
>> >> >> I thought this one is cached. Can you show me pgpool.log with
>> >> >> log_per_node_statement enabled?
>> >> >> --
>> >> >> Tatsuo Ishii
>> >> >> SRA OSS, Inc. Japan
>> >> >> English: http://www.sraoss.co.jp/index_en.php
>> >> >> Japanese: http://www.sraoss.co.jp
>> >> >>
>> >> >> > *Nathan Brennan* *BCS*
>> >> >> > Lead Developer
>> >> >> >
>> >> >> > *T:* +61 8 9482 3981 | *E:* nathan at healthengine.com.au
>> >> >> >
>> >> >> > <http://healthengine.com.au/>      <
>> https://twitter.com/healthengine>
>> >> >> > <https://www.facebook.com/HealthEngine>
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > On Wed, Dec 4, 2013 at 12:51 PM, Tatsuo Ishii <
>> ishii at postgresql.org>
>> >> >> wrote:
>> >> >> >
>> >> >> >> > Hi,
>> >> >> >> >
>> >> >> >> > Is there a way to cache these queries or turn them off as
>> pgpool is
>> >> >> doing
>> >> >> >> > thousands of them a second.I tried setting relcache_expire=60
>> but
>> >> it
>> >> >> >> > doesn't seem to help.
>> >> >> >> >
>> >> >> >> > example queries:
>> >> >> >> >
>> >> >> >> > SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE
>> >> c.relname
>> >> >> =
>> >> >> >> > 'table' AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog'
>> >> >> >> >  SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE
>> >> >> c.relname =
>> >> >> >> > 'table' AND c.relnamespace = n.oid AND n.nspname ~ '^pg_temp_'
>> >> >> >>
>> >> >> >> You can avoid temp table related queries by turning
>> check_temp_table
>> >> >> >> to off (of course you need to be sure that you are not using temp
>> >> >> >> tables).
>> >> >> >>
>> >> >> >> Also relcache_expire = 0 will be more effective if you want to
>> reduce
>> >> >> >> the number of queries.
>> >> >> >> --
>> >> >> >> Tatsuo Ishii
>> >> >> >> SRA OSS, Inc. Japan
>> >> >> >> English: http://www.sraoss.co.jp/index_en.php
>> >> >> >> Japanese: http://www.sraoss.co.jp
>> >> >> >>
>> >> >>
>> >>
>>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pool_select_walker.c.patch
Type: text/x-patch
Size: 467 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20131204/c0de3053/attachment.bin>