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

[Tatsuo Ishii]

Hi Peng-san,

Thank you for the review!

I have pushed to all supported branches back to v4.1
(statement_level_load_balance first appeared in v4.1).

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

> 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/