[pgpool-general: 7674] Re: Configuration for multi-region deployment

[Tatsuo Ishii]

Hi,

[snip]

> The problem is that read only queries to PgPool Site B is still extremely slow and facing a lot of delays, by simply removing node 3 ( the other pgpool master ) hence connecting to a read only cluster, the queries are then much faster.
> I'm speaking about 5 minutes query when Node 3 is connected vs 5 seconds once I remove Node 3 even in that in both cases only the standby nodes are the ones executing the queries.

My guess is, you are bitten by this:

> Just a small hint, all our queries are being executed in a READ ONLY Transactions.

BEGIN is always sent to primary and one of standbys. So your SELECT
will not be executed until BEGIN command finishes on primary, which is
slow.

You can check it by sending "SELECT 1" to the US pgpool by using
psql. If it's fast, mu guess is being proven to be correct.

BTW,

> master_slave_mode = 'on'
> master_slave_sub_mode = 'stream'

You don't need these. They are obsoleted and replaced by:

backend_clustering_mode = 'streaming_replication'
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp