[pgpool-hackers: 3912] Re: Proposal: If replication delay exceeds delay_threshold, elect a new load balance node with less delay

[KAWAMOTO Masaya]

Hi Ishii-san,

Thank you for your comment.

On Wed, 26 May 2021 19:22:59 +0900 (JST)
Tatsuo Ishii <ishii ¡÷ sraoss.co.jp> wrote:

> >> Hi Peng.
> >> 
> >> I modified my patch.
> >> 
> >> The select_lower_delay_load_balance_node.patch_r3 includes documentation update.
> > 
> > It seems you coding sytle is against our standard. We inherit the
> > coding style of PostgreSQL:
> > https://www.postgresql.org/docs/13/source-format.html
> 
> Sorry, I was looking into the previous patch. Please disregard my comment.
> 
> - comment to the docs
> 
> > +      This parameter is only valid when delay_threshold is set to larger than 0.
> 
> +      This parameter is valid only when delay_threshold is set greater than 0.
> 
> > +      When set to on, if load balancing node is delayed over delay_threshold
> > +      Pgpool-II send the query to not the primary node but the least delay standby which is set backend_weight to larger than 0.
> 
> +      When set to on, if the delay of the load balancing node is greater than delay_threshold,
> +      <productname>Pgpool-II</productname> does not send read queries to the primary node but to the least delay standby node with backend_weight greater than 0.
> 
> > +      If all standby nodes are delayed over delay_threshold, Pgpool-II send to the primary. Default is off.
> 
> +      If delay of all standby nodes are greater than delay_threshold, <productname>Pgpool-II</productname> sends read queries to the primary. Default is off.

I fixed these.

> 
> >> The test.sh is the regression test script.
> 
> The test script only works with PostgreSQL 12 or later because the
> test rely on the feature that only exists in PostgreSQL 12 or later:
> postgresql.conf's include_if_exists feature.

Sorry for I had not checked enough cases.
I modified test.sh to use pg_xlog/wal_replay_pause/resume().
Could you please check it again? 

> You need patches for src/util/pool_process_reporting.c so that "show
> pool_status" includes prefer_lower_delay_standby.
> 
> Finally I have briefly checked the feature using pgbench.
> 
> $ pgpool_setup -n 3
> 
> $ echo "prefer_lower_delay_standby = on" >> etc/pgpool.conf 
> $ echo "statement_level_load_balance = on" >> etc/pgpool.conf 
> $ ./startall
> $ psql -p 11003 -c "select pg_wal_replay_pause()" test
> $ pgbench -i -p 11000 test
> $ psql -p 11000 -c "show pool_nodes" test
>  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       | /tmp     | 11002 | up     | up        | 0.333333  | primary | primary | 0          | false             | 0                 |                   |                        | 2021-05-26 18:55:13
>  1       | /tmp     | 11003 | up     | up        | 0.333333  | standby | standby | 0          | false             | 13188800          | streaming         | async                  | 2021-05-26 18:55:13
>  2       | /tmp     | 11004 | up     | up        | 0.333333  | standby | standby | 0          | true              | 0                 | streaming         | async                  | 2021-05-26 18:55:13
> (3 rows)
> 
> $ pgbench -p 11000 -n -S -t 100 test
> 
> t-ishii$ psql -p 11000 -c "show pool_nodes" test
>  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       | /tmp     | 11002 | up     | up        | 0.333333  | primary | primary | 29         | true              | 0                 |                   |                        | 2021-05-26 18:55:13
>  1       | /tmp     | 11003 | up     | up        | 0.333333  | standby | standby | 0          | false             | 13188800          | streaming         | async                  | 2021-05-26 18:55:13
>  2       | /tmp     | 11004 | up     | up        | 0.333333  | standby | standby | 73         | false             | 0                 | streaming         | async                  | 2021-05-26 18:55:13
> (3 rows)
> 
> Good news is, with prefer_lower_delay_standby, SELECT is not sent to
> standby node 1 because its replication delay 13188800 exceeds
> delay_threshold 10000000. However, select_cnt of primary and standby
> node 2 looks strange since lb_weight of both nodes are
> identical. Because pgbench issues 100 SELECTs, select_cnt of both
> nodes should be close to 50 and 50, no?

This is the expected result. 
prefer_lower_delay_standby is effective when the selected node is a standby
node. In your example, all nodes have the same weight, so if it set to on the 
queries were sent to node 2 when node 1 was selected. 
Other standby nodes take over the processing of the delayed node.


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


-- 
KAWAMOTO Masaya <kawamoto ¡÷ sraoss.co.jp>
SRA OSS, Inc. Japan
-------------- next part --------------
¥Æ¥­¥¹¥È·Á¼°°Ê³°¤ÎźÉÕ¥Õ¥¡¥¤¥ë¤òÊݴɤ·¤Þ¤·¤¿...
¥Õ¥¡¥¤¥ë̾: select_lower_delay_load_balance_node.patch_r4
·¿:         application/octet-stream
¥µ¥¤¥º:     18707 ¥Ð¥¤¥È
ÀâÌÀ:       ̵¤·
URL:        <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20210603/2748d3f5/attachment-0001.obj>