[pgpool-general: 6592] Re: PostgreSQL10, PgPool-II 4.0.5 geographically distributed

[Tatsuo Ishii]

> Hi,
> I try to speed up our application by moving READ-ONLY PostgreSQL replica near our Far East office.
> 
> The idea is to keep RW PostgreSQL database in Europe and a READ-ONLY PostgreSQL replica (streaming) in Far East; then create a pgpool in Far East  that write only in Europe (few big write) and read (many little read) on Far East database, and create a new application server Far East.
> 
> I created a test environment, it works fine but is very slow. It seems that pgpool route statements correctly (RW to Europe, and READ to Far East) but there are many query to pg_catalog (RW) that seem to slow down the application server.
> 
> This is an example;
> SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = pg_catalog.to_regclass('"XXX"."YYYY"') AND c.relpersistence = 'u'
> 
> Can someone suggest how to go on?

They are issued by Pgpool-II itself to get some information from
system catalogs mainly regarding tables appearing in the
queries. Unfortunately Pgpool-II always tries to send the queries to
primary PostgreSQL, which is in Europe in your case.

Good news is, Pgpool-II has a "relation cache" to preserve the query
result to the system catalog. For example,

SELECT * FROM foo WHERE i = 1;
SELECT * FROM foo WHERE i = 2;

The first SELECT needs to access the primary PostgreSQL, but second
one doesn't need to access because the information regarding "foo" is
already cached.

Also there are some parameters to reduce the number of queries. If
your apps don't use unlogged tables at all, you can set
check_unlogged_table to on to eliminate system catalog queries
regarding unlogged tables. Same thing can be said to check_temp_table.
See the manual for more details.

http://www.pgpool.net/docs/latest/en/html/runtime-misc.html

Finally, please note that the relation cache basically lives in
process private memory, which is bound to a process. So even if a
relation cache is created to for a table, in different process the
relation cache might not be created yet. After all, until a relation
cache entry is created in all process, queries continue to sent to
PostgreSQL.

Upcoming 4.1 will overcome the issue by creating relation cache in
shared memory. If a session creates a relation cache entry in the
shared memory, other sessions will get the cache result by looking at
the shared relation cache. We plan to release 4.1 in this fall.

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