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

[Tatsuo Ishii]

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