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

[Nathan Brennan]

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