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

[Tatsuo Ishii]

Hi,

I made a commit to enhacement performance in your case toward Pgpool-II 4.1.
https://www.pgpool.net/pipermail/pgpool-hackers/2019-June/003342.html

With this, Pgpool-II node in your Far East office will not send
internal queries (like SELECT count(*) FROM pg_catalog...) to your
primary PostgreSQL server located in your Europe office.

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

>> 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
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general