[pgpool-hackers: 3930] Re: Proposal: If replication delay exceeds delay_threshold, elect a new load balance node with less delay
KAWAMOTO Masaya
kawamoto at sraoss.co.jp
Tue Jun 15 16:28:25 JST 2021
Hi Ishii-san,
On Thu, 10 Jun 2021 12:14:19 +0900 (JST)
Tatsuo Ishii <ishii ¡÷ sraoss.co.jp> wrote:
> Hi Kawamoto-san,
>
> > Hi Ishii-san,
> >
> > I modified my patch.
> >
> > Pleas see the following test result.
> > I tested on a 5 node cluster.
> >
> > First, in the case that the node 3 is only delayed, the node 0 is the primary
> > and the node 1,2,4 are the lowest delay standbys. The result is that pgpool
> > sent the 17% queries to the primary and the 27~30% queries to the each
> > lowest delay standbys. This result is close to the hoped result that the 20%
> > to the primary and the 30% to the each standbys.
> >
> > Second, in the case that the node 3 and 4 are delayed, the node 0 is the
> > primary and the node 1,2 are the lowest delay standbys. The result is the
> > 19% to the primary and the 39~40% to the each lowest delay standbys. I
> > think this is very good result.
> >
> > What do you think?
>
> The proposed behavior looks sane and good to me. However then you need
> to change the docs because the doc stats:
>
> 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 the least delay standby with
> backend_weight to greater than 0.
>
> The doc says "<productname>Pgpool-II</productname> does not send read
> queries to the primary node" but according to your proposal Pgpool-II
> sends to the primary node as well as standbys.
>
I had not describe if the primary was selected as the load balancing node.
I wanted to say:
This parameter is valid only when delay_threshold is set to greater 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
the least delay standby with backend_weight to greater than 0. If delay of all standby
nodes are greater than delay_threshold or the primary selected as the load balancing node
first, <productname>Pgpool-II<productname> sends to the primary.
Default is off.
> > ========
> > -bash-4.2$ psql -p 11000 -c "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 | /tmp | 11002 | up | up | 0.200000 | primary | primary | 0 | true | 0 | | | 2021-06-09 07:23:37
> > 1 | /tmp | 11003 | up | up | 0.200000 | standby | standby | 0 | false | 0 | streaming | async | 2021-06-09 07:23:37
> > 2 | /tmp | 11004 | up | up | 0.200000 | standby | standby | 0 | false | 0 | streaming | async | 2021-06-09 07:23:37
> > 3 | /tmp | 11005 | up | up | 0.200000 | standby | standby | 0 | false | 0 | streaming | async | 2021-06-09 07:23:37
> > 4 | /tmp | 11006 | up | up | 0.200000 | standby | standby | 0 | false | 0 | streaming | async | 2021-06-09 07:23:37
> > (5 rows)
> >
> > -bash-4.2$ psql -p 11005 -c "select pg_wal_replay_pause()"
> > pg_wal_replay_pause
> > ---------------------
> >
> > (1 row)
> >
> > -bash-4.2$ pgbench -p 11000 -i test
> >
> > -bash-4.2$ pgbench -p 11000 -n -S -t 400 test
> >
> > -bash-4.2$ psql -p 11000 -c "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 | /tmp | 11002 | up | up | 0.200000 | primary | primary | 69 | false | 0 | | | 2021-06-09 07:05:16
> > 1 | /tmp | 11003 | up | up | 0.200000 | standby | standby | 106 | false | 0 | streaming | async | 2021-06-09 07:05:16
> > 2 | /tmp | 11004 | up | up | 0.200000 | standby | standby | 108 | false | 0 | streaming | async | 2021-06-09 07:05:16
> > 3 | /tmp | 11005 | up | up | 0.200000 | standby | standby | 0 | false | 13158872 | streaming | async | 2021-06-09 07:05:16
> > 4 | /tmp | 11006 | up | up | 0.200000 | standby | standby | 119 | true | 0 | streaming | async | 2021-06-09 07:05:16
> > (5 rows)
> >
> > -bash-4.2$ psql -p 11006 -c "select pg_wal_replay_pause()"
> > pg_wal_replay_pause
> > ---------------------
> >
> > (1 row)
> >
> > -bash-4.2$ pgbench -p 11000 -i test
> >
> > -bash-4.2$ pgbench -p 11000 -n -S -t 400 test
> >
> > -bash-4.2$ psql -p 11000 -c "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 | /tmp | 11002 | up | up | 0.200000 | primary | primary | 69 | true | 0 | | | 2021-06-09 07:05:16
> > 1 | /tmp | 11003 | up | up | 0.200000 | standby | standby | 106 | false | 0 | streaming | async | 2021-06-09 07:05:16
> > 2 | /tmp | 11004 | up | up | 0.200000 | standby | standby | 108 | false | 0 | streaming | async | 2021-06-09 07:05:16
> > 3 | /tmp | 11005 | up | up | 0.200000 | standby | standby | 0 | false | 26195408 | streaming | async | 2021-06-09 07:05:16
> > 4 | /tmp | 11006 | up | up | 0.200000 | standby | standby | 119 | false | 13036536 | streaming | async | 2021-06-09 07:05:16
> > (5 rows)
> >
> > -bash-4.2$ pgbench -p 11000 -n -S -t 300 test
> >
> > -bash-4.2$ psql -p 11000 -c "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 | /tmp | 11002 | up | up | 0.200000 | primary | primary | 127 | false | 0 | | | 2021-06-09 07:05:16
> > 1 | /tmp | 11003 | up | up | 0.200000 | standby | standby | 225 | true | 0 | streaming | async | 2021-06-09 07:05:16
> > 2 | /tmp | 11004 | up | up | 0.200000 | standby | standby | 233 | false | 0 | streaming | async | 2021-06-09 07:05:16
> > 3 | /tmp | 11005 | up | up | 0.200000 | standby | standby | 0 | false | 26268544 | streaming | async | 2021-06-09 07:05:16
> > 4 | /tmp | 11006 | up | up | 0.200000 | standby | standby | 119 | false | 13109672 | streaming | async | 2021-06-09 07:05:16
> > (5 rows)
> > ========
>
> Thanks for the resived patch. Here are comments:
>
> 1) The patch seems to have extra space.
> /home/t-ishii/select_lower_delay_load_balance_node.patch_r6:204: trailing whitespace.
> * The new load balancing node is seleted from the
> warning: 1 line adds whitespace errors.
>
> 2) You don't need to include patches fopr src/parser/gram_minimal.c
> because it's automatically generated from gram_minimal.y.
gram_minimal.c was upgraded.
This seems to have been a bug.
https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commitdiff;h=5089e8ea20fd83c0ca6e1384f266a6f5d73d49a5
> 3) test.sh fails
>
> psql: error: FATAL: database "t-ishii" does not exist
>
> Probably need to add export below:
>
> source $TESTLIBS
> TESTDIR=testdir
> PG_CTL=$PGBIN/pg_ctl
> PSQL="$PGBIN/psql -X "
> + export PGDATABASE=test
I fixed 1), 3) and changed the doc.
Could you please re-review?
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
Best regards.
--
KAWAMOTO Masaya <kawamoto ¡÷ sraoss.co.jp>
SRA OSS, Inc. Japan
-------------- next part --------------
¥Æ¥¥¹¥È·Á¼°°Ê³°¤ÎźÉÕ¥Õ¥¡¥¤¥ë¤òÊݴɤ·¤Þ¤·¤¿...
¥Õ¥¡¥¤¥ë̾: select_lower_delay_load_balance_node.patch_r7
·¿: application/octet-stream
¥µ¥¤¥º: 20824 ¥Ð¥¤¥È
ÀâÌÀ: ̵¤·
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20210615/8c463a05/attachment-0001.obj>
More information about the pgpool-hackers
mailing list