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

[Tatsuo Ishii]

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