<div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">2018-04-13 13:16 GMT+09:00 Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="HOEnZb"><div class="h5">> Hello,<br>
> <br>
> At first, I'm sorry I'm not good at English.<br>
> <br>
> <br>
> I'd installed Pgpool 3.7.2 and two Postgres 10.3 instances, and configure a<br>
> master-slave mode cluster. I turned on also load_balancing_mode.<br>
> <br>
> When I was testing and looking into logs, I found that pgpool sent backend0<br>
> several extray queries "SELECT count(*) ..." for every simple select query:<br>
> <br>
> <br>
> # what I did: (23140 is pgpool port)<br>
> $ psql -h 127.0.0.1 -p 23140 -U postgres test -c "select * from mytable;"<br>
> <br>
> <br>
> # what the pgpool log showed (timestamp omitted)<br>
> pid 1583: LOG: DB node id: 0 backend pid: 469 statement: SELECT count(*)<br>
> from (SELECT has_function_privilege('<wbr>postgres', 'to_regclass(cstring)',<br>
> 'execute') WHERE EXISTS(SELECT * FROM pg_catalog.pg_proc AS p WHERE<br>
> p.proname = 'to_regclass')) AS s<br>
> <br>
> pid 1583: LOG: DB node id: 0 backend pid: 469 statement: SELECT count(*)<br>
> FROM pg_catalog.pg_class AS c WHERE c.relname = 'pg_namespace'<br>
> <br>
> pid 1583: LOG: DB node id: 0 backend pid: 469 statement: SELECT count(*)<br>
> FROM pg_class AS c, pg_namespace AS n WHERE c.oid =<br>
> to_regclass('"mytable"') AND c.relnamespace = n.oid AND n.nspname =<br>
> 'pg_catalog'<br>
> <br>
> pid 1583: LOG: DB node id: 0 backend pid: 469 statement: SELECT count(*)<br>
> FROM pg_catalog.pg_class AS c, pg_attribute AS a WHERE c.relname =<br>
> 'pg_class' AND a.attrelid = c.oid AND a.attname = 'relistemp'<br>
> <br>
> pid 1583: LOG: DB node id: 0 backend pid: 469 statement: SELECT count(*)<br>
> FROM pg_class AS c, pg_namespace AS n WHERE c.relname = 'mytable' AND<br>
> c.relnamespace = n.oid AND n.nspname ~ '^pg_temp_'<br>
> <br>
> pid 1583: LOG: DB node id: 0 backend pid: 469 statement: SELECT count(*)<br>
> FROM pg_catalog.pg_class AS c, pg_catalog.pg_attribute AS a WHERE c.relname<br>
> = 'pg_class' AND a.attrelid = c.oid AND a.attname = 'relpersistence'<br>
> <br>
> pid 1583: LOG: DB node id: 0 backend pid: 469 statement: SELECT count(*)<br>
> FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"mytable"') AND<br>
> c.relpersistence = 'u'<br>
> <br>
> pid 1583: LOG: DB node id: 1 backend pid: 2312 statement: select * from<br>
> mytable;<br>
> <br>
> <br>
> I tried more cases and observed:<br>
> 1) INSERT and UPDATE queries do not make these extra queries. Only SELECT<br>
> query does.<br>
> <br>
> 2) The extra queries were always sent to backend#0, regardless of whether<br>
> it was primary or standby.<br>
> <br>
> <br>
> I'd like to ask:<br>
> 1) Is this a normal operation or some signal that something is wrong?<br>
<br>
</div></div>Yes. SELECTs issues queries against PostgreSQL system catalogs to know<br>
if the table is a temporary table or not etc.<br>
<span class=""><br>
> 2) If it is a ordinary operation, is it ok? I mean, couldn't it be too much<br>
> overhead, if 7 more queries are sent for every 1 "SELECT" query?<br>
<br>
</span>Yes. Although for the first time of a SELECT to a table these queries<br>
are issued, the results are cached and for the next SELECT those<br>
extra queries are no longer issued as long as same table is involved.<br>
The cache is maintained in the pgpool child process memory space, that<br>
means as long as the process continues to run, the cache can be used.<br>
<br></blockquote><div><br></div><div>Ooops. I see. </div><div><br></div><div>I was creating new psql instance to send a SELECT query so that I saw so much extra queries.</div><div><br></div><div>In an interactive mode of psql, only the first query issues that extra query.</div><div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
The number of default cache entry per pgpool process is defined as<br>
follows in pgpool.conf:<br>
<br>
relcache_size = 256<br>
<br>
If you rarely see "pool_search_relcache: cache replacement happend" in<br>
the pgpool log, then the cache entry size is big enough. If you see<br>
the message too often, try to increase it.<br></blockquote><div><br></div><div>Thank you very much!</div><div><br></div><div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_<wbr>en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.<wbr>jp</a><br>
</blockquote></div><br></div><div class="gmail_extra"><br></div><div class="gmail_extra">gypark</div></div>