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

Nathan Brennan nathan at healthengine.com.au
Wed Dec 4 16:29:40 JST 2013


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


More information about the pgpool-general mailing list