[pgpool-hackers: 3342] Routing relcache queries to load balance node

Tatsuo Ishii ishii at sraoss.co.jp
Wed Jun 19 15:05:37 JST 2019

I was inspired by this, created a patch to allow to make relcache
related queries against standby server, rather than priary server.

New parameter "relcache_query_target" is introduced in the patch. If
it's set to 'load_balance_node', relacahe queries will be routed to
load balance node. To be sure not route to primary, set backend_weigt
of primary to 0.

Patch attached. Comments and suggestions are welcome.

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

From: Giorgio Perotto <g.perotto at outlook.it> Subject: [pgpool-general:
6591] PostgreSQL10, PgPool-II 4.0.5 geographically distributed Date:
Tue, 11 Jun 2019 10:14:36 +0000 Message-ID:
<HE1PR0201MB22039114B6ED507CAA8AE7BF8EED0 at HE1PR0201MB2203.eurprd02.prod.outlook.com>

> 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?
> Thanks.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: relcache_target.diff
Type: application/octet-stream
Size: 14769 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20190619/f5d92ac2/attachment.obj>

More information about the pgpool-hackers mailing list