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

[Tatsuo Ishii]

Sice there was no objection, I have just committed this.

https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=46986ebc0be77e4b011b94b097789fec9e3f5b0f

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

> 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
> Japanese:http://www.sraoss.co.jp
> 
> 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.