[pgpool-hackers: 4424] Re: statement_level_load_balance does not honor load_balance_mode

[Bo Peng]

Thank you, Ishii-san.

I have tested your patch.
It seems good to me.

On Wed, 07 Feb 2024 11:36:58 +0900 (JST)
Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> While taking care of this:
> [pgpool-general: 8998] Massive performance and latency issues when remote replica is attached to cluster.
> https://www.pgpool.net/pipermail/pgpool-general/2024-January/009059.html
> 
> I found that statement_level_load_balance does not honor
> load_balance_mode. if statement_level_load_balance is on,
> pool_setall_node_to_be_sent(), which is responsible to decide where to
> send queries such as SET, BEGIN, END, SAVEPOINT etc., sets
> the query_context->where_to_send[] map to all live nodes even if
> load_balance_mode is off. When load_balance_mode is off, pgpool only
> sends queries to the primary node in streaming replication mode, thus
> it's just a waste of resource to send queries to all live
> nodes. Unfortunately in the case above, the user has one of the
> standby nodes in a remote network, and sending queries to the remote
> node causes a slow down due to the network latency.
> 
> t-ishii$ psql -p 11000 test
> psql (16.0)
> Type "help" for help.
> 
> test=# show pool_nodes;
>  node_id | hostname  | port  | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
> ---------+-----------+-------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
>  0       | localhost | 11002 | up     | up        | 0.333333  | primary | primary | 0          | true              | 0                 |                   |                        | 2024-02-07 11:29:59
>  1       | localhost | 11003 | up     | up        | 0.333333  | standby | standby | 0          | false             | 0                 | streaming         | async                  | 2024-02-07 11:29:59
>  2       | localhost | 11004 | up     | up        | 0.333333  | standby | standby | 0          | false             | 0                 | streaming         | async                  | 2024-02-07 11:29:59
> (3 rows)
> 
> test=# pgpool show load_balance_mode;
>  load_balance_mode 
> -------------------
>  off
> (1 row)
> 
> test=# pgpool show statement_level_load_balance;
>  statement_level_load_balance 
> ------------------------------
>  on
> (1 row)
> 
> test=# begin;
> NOTICE:  DB node id: 0 statement: begin;
> NOTICE:  DB node id: 1 statement: begin;
> NOTICE:  DB node id: 2 statement: begin;
> BEGIN
> 
> Attached is a patch against pool_setall_node_to_be_sent() to add
> checking of load_balance_mode. After the patch "begin" is sent to only
> primary node even if statement_level_load_balance is on,
> 
> test=# begin;
> NOTICE:  DB node id: 0 statement: begin;
> BEGIN
> 
> Comments/suggestions are welcome.
> 
> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS LLC
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
TEL: 03-5979-2701 FAX: 03-5979-2702
URL: https://www.sraoss.co.jp/