[pgpool-hackers: 3937] Re: Proposal: If replication delay exceeds delay_threshold, elect a new load balance node with less delay
Tatsuo Ishii
ishii at sraoss.co.jp
Fri Jun 18 09:55:18 JST 2021
Hi Kawamoto-san,
Thank you for revising the patch.
> Hi Ishii-san,
>
>
> On Thu, 10 Jun 2021 12:14:19 +0900 (JST)
> Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>
>> Hi Kawamoto-san,
>>
>> > Hi Ishii-san,
>> >
>> > I modified my patch.
--- a/doc/src/sgml/stream-check.sgml
+++ b/doc/src/sgml/stream-check.sgml
+ When set to on, if the delay of the load balancing node is greater than delay_threshold
You need a comma (,), at the end of the sentence.
Besides this, all look good to me.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
>> >
>> > 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 at sraoss.co.jp>
> SRA OSS, Inc. Japan
More information about the pgpool-hackers
mailing list