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

[Raymundo]

2018-04-13 13:16 GMT+09:00 Tatsuo Ishii <ishii at sraoss.co.jp>:

> > 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.
>
>
Ooops. I see.

I was creating new psql instance to send a SELECT query so that I saw so
much extra queries.

In an interactive mode of psql, only the first query issues that extra
query.


> 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.
>

Thank you very much!



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


gypark
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20180413/0db4ffff/attachment.html>