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

[Nathan Brennan]

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 --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20131204/a2383a48/attachment-0001.html>