<div dir="ltr"><br><div class="gmail_extra"><div><div dir="ltr"><div style="font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">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.</div>
</div></div><br><div class="gmail_quote">On Wed, Dec 4, 2013 at 3:32 PM, Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Are you sure that those "table" are exactly the same one?<br>
<div class="HOEnZb"><div class="h5">--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
<br>
> Ok I scanned the log again and found this:<br>
><br>
> 2013-12-04 07:02:36 LOG: pid 17148: DB node id: 0 backend pid: 6475<br>
> statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname =<br>
> 'table' AND c.relpersistence = 'u'<br>
> 2013-12-04 07:02:46 LOG: pid 17148: DB node id: 0 backend pid: 6475<br>
> statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname =<br>
> 'table' AND c.relpersistence = 'u'<br>
> 2013-12-04 07:02:49 LOG: pid 17148: DB node id: 0 backend pid: 6475<br>
> statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname =<br>
> 'table' AND c.relpersistence = 'u'<br>
><br>
> that looks like the same pid not caching.<br>
><br>
> On Wed, Dec 4, 2013 at 3:21 PM, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>> wrote:<br>
><br>
>> The cache is per process base, not cluster wide.<br>
>><br>
>> That means, unless you find an evidence that same pid process issues<br>
>> same catalog access more than once, it can be said that the cache is<br>
>> working.<br>
>> --<br>
>> Tatsuo Ishii<br>
>> SRA OSS, Inc. Japan<br>
>> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>><br>
>> > I have just cut out some parts of the log, as it generates mbs of logs a<br>
>> > minute as I believe this is what you want, as you can see it doesn't seem<br>
>> > to be caching:<br>
>> ><br>
>> > 2013-12-04 07:02:19 LOG: pid 17148: DB node id: 0 backend pid: 6475<br>
>> > statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname<br>
>> =<br>
>> > 'table' AND c.relpersistence = 'u'<br>
>> > 2013-12-04 07:02:19 LOG: pid 17173: DB node id: 0 backend pid: 6463<br>
>> > statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname<br>
>> =<br>
>> > 'table' AND c.relpersistence = 'u'<br>
>> > 2013-12-04 07:02:19 LOG: pid 17179: DB node id: 0 backend pid: 6462<br>
>> > statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname<br>
>> =<br>
>> > 'table' AND c.relpersistence = 'u'<br>
>> ><br>
>> > let me know if this is not what you wanted, we are currently using<br>
>> pgpool:<br>
>> > 3.3.0 with postgres 9.1<br>
>> ><br>
>> > *Nathan Brennan* *BCS*<br>
>> > Lead Developer<br>
>> ><br>
>> > *T:* +61 8 9482 3981 | *E:* <a href="mailto:nathan@healthengine.com.au">nathan@healthengine.com.au</a><br>
>> ><br>
>> > <<a href="http://healthengine.com.au/" target="_blank">http://healthengine.com.au/</a>> <<a href="https://twitter.com/healthengine" target="_blank">https://twitter.com/healthengine</a>><br>
>> > <<a href="https://www.facebook.com/HealthEngine" target="_blank">https://www.facebook.com/HealthEngine</a>><br>
>> ><br>
>> ><br>
>> ><br>
>> > On Wed, Dec 4, 2013 at 2:40 PM, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>><br>
>> wrote:<br>
>> ><br>
>> >> > ok thanks,<br>
>> >> ><br>
>> >> > I have disabled the check_temp_table as we don't use temp tables which<br>
>> >> > removes the pg_temp queries, however there is still alot of queries<br>
>> for<br>
>> >> > SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname =<br>
>> 'table'<br>
>> >> AND<br>
>> >> > c.relpersistence = 'u' to me this seems like a check to see if the<br>
>> table<br>
>> >> > exists, is there a way to cache this ?<br>
>> >><br>
>> >> I thought this one is cached. Can you show me pgpool.log with<br>
>> >> log_per_node_statement enabled?<br>
>> >> --<br>
>> >> Tatsuo Ishii<br>
>> >> SRA OSS, Inc. Japan<br>
>> >> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> >> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>> >><br>
>> >> > *Nathan Brennan* *BCS*<br>
>> >> > Lead Developer<br>
>> >> ><br>
>> >> > *T:* <a href="tel:%2B61%208%209482%203981" value="+61894823981">+61 8 9482 3981</a> | *E:* <a href="mailto:nathan@healthengine.com.au">nathan@healthengine.com.au</a><br>
>> >> ><br>
>> >> > <<a href="http://healthengine.com.au/" target="_blank">http://healthengine.com.au/</a>> <<a href="https://twitter.com/healthengine" target="_blank">https://twitter.com/healthengine</a>><br>
>> >> > <<a href="https://www.facebook.com/HealthEngine" target="_blank">https://www.facebook.com/HealthEngine</a>><br>
>> >> ><br>
>> >> ><br>
>> >> ><br>
>> >> > On Wed, Dec 4, 2013 at 12:51 PM, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>><br>
>> >> wrote:<br>
>> >> ><br>
>> >> >> > Hi,<br>
>> >> >> ><br>
>> >> >> > Is there a way to cache these queries or turn them off as pgpool is<br>
>> >> doing<br>
>> >> >> > thousands of them a second.I tried setting relcache_expire=60 but<br>
>> it<br>
>> >> >> > doesn't seem to help.<br>
>> >> >> ><br>
>> >> >> > example queries:<br>
>> >> >> ><br>
>> >> >> > SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE<br>
>> c.relname<br>
>> >> =<br>
>> >> >> > 'table' AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog'<br>
>> >> >> > SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE<br>
>> >> c.relname =<br>
>> >> >> > 'table' AND c.relnamespace = n.oid AND n.nspname ~ '^pg_temp_'<br>
>> >> >><br>
>> >> >> You can avoid temp table related queries by turning check_temp_table<br>
>> >> >> to off (of course you need to be sure that you are not using temp<br>
>> >> >> tables).<br>
>> >> >><br>
>> >> >> Also relcache_expire = 0 will be more effective if you want to reduce<br>
>> >> >> the number of queries.<br>
>> >> >> --<br>
>> >> >> Tatsuo Ishii<br>
>> >> >> SRA OSS, Inc. Japan<br>
>> >> >> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> >> >> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>> >> >><br>
>> >><br>
>><br>
</div></div></blockquote></div><br></div></div>