[pgpool-general: 6041] Re: Question about extra queries created by pgpool master_slave_mode

[Tatsuo Ishii]

> Hello,
> 
> At first, I'm sorry I'm not good at English.
> 
> 
> I'd installed Pgpool 3.7.2 and two Postgres 10.3 instances, and configure a
> master-slave mode cluster. I turned on also load_balancing_mode.
> 
> When I was testing and looking into logs, I found that pgpool sent backend0
> several extray queries "SELECT count(*) ..." for every simple select query:
> 
> 
> # what I did: (23140 is pgpool port)
> $ psql -h 127.0.0.1 -p 23140 -U postgres test -c "select * from mytable;"
> 
> 
> # what the pgpool log showed (timestamp omitted)
> pid 1583: LOG:  DB node id: 0 backend pid: 469 statement: SELECT count(*)
> from (SELECT has_function_privilege('postgres', 'to_regclass(cstring)',
> 'execute') WHERE EXISTS(SELECT * FROM pg_catalog.pg_proc AS p WHERE
> p.proname = 'to_regclass')) AS s
> 
> pid 1583: LOG:  DB node id: 0 backend pid: 469 statement: SELECT count(*)
> FROM pg_catalog.pg_class AS c WHERE c.relname = 'pg_namespace'
> 
> pid 1583: LOG:  DB node id: 0 backend pid: 469 statement: SELECT count(*)
> FROM pg_class AS c, pg_namespace AS n WHERE c.oid =
> to_regclass('"mytable"') AND c.relnamespace = n.oid AND n.nspname =
> 'pg_catalog'
> 
> pid 1583: LOG:  DB node id: 0 backend pid: 469 statement: SELECT count(*)
> FROM pg_catalog.pg_class AS c, pg_attribute AS a WHERE c.relname =
> 'pg_class' AND a.attrelid = c.oid AND a.attname = 'relistemp'
> 
> pid 1583: LOG:  DB node id: 0 backend pid: 469 statement: SELECT count(*)
> FROM pg_class AS c, pg_namespace AS n WHERE c.relname = 'mytable' AND
> c.relnamespace = n.oid AND n.nspname ~ '^pg_temp_'
> 
> pid 1583: LOG:  DB node id: 0 backend pid: 469 statement: SELECT count(*)
> FROM pg_catalog.pg_class AS c, pg_catalog.pg_attribute AS a WHERE c.relname
> = 'pg_class' AND a.attrelid = c.oid AND a.attname = 'relpersistence'
> 
> pid 1583: LOG:  DB node id: 0 backend pid: 469 statement: SELECT count(*)
> FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"mytable"') AND
> c.relpersistence = 'u'
> 
> pid 1583: LOG:  DB node id: 1 backend pid: 2312 statement: select * from
> mytable;
> 
> 
> I tried more cases and observed:
> 1) INSERT and UPDATE queries do not make these extra queries. Only SELECT
> query does.
> 
> 2) The extra queries were always sent to backend#0, regardless of whether
> it was primary or standby.
> 
> 
> I'd like to ask:
> 1) Is this a normal operation or some signal that something is wrong?

Yes. SELECTs issues queries against PostgreSQL system catalogs to know
if the table is a temporary table or not etc.

> 2) If it is a ordinary operation, is it ok? I mean, couldn't it be too much
> overhead, if 7 more queries are sent for every 1 "SELECT" query?

Yes. Although for the first time of a SELECT to a table these queries
are issued, the results are cached and for the next SELECT those
extra queries are no longer issued as long as same table is involved.
The cache is maintained in the pgpool child process memory space, that
means as long as the process continues to run, the cache can be used.

The number of default cache entry per pgpool process is defined as
follows in pgpool.conf:

relcache_size = 256

If you rarely see "pool_search_relcache: cache replacement happend" in
the pgpool log, then the cache entry size is big enough. If you see
the message too often, try to increase it.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp