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

[KAWAMOTO Masaya]

On Thu, 03 Jun 2021 15:06:14 +0900 (JST)
Tatsuo Ishii <ishii ¡÷ sraoss.co.jp> wrote:

> >> 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.
> 
> Ok.
> 
> So this time I did the same test on a 4 node cluster.
> 
> t-ishii$ pgbench -p 11000 -n -S -t 100 test
> transaction type: <builtin: select only>
> scaling factor: 1
> query mode: simple
> number of clients: 1
> number of threads: 1
> number of transactions per client: 100
> number of transactions actually processed: 100/100
> latency average = 0.300 ms
> tps = 3336.064903 (including connections establishing)
> tps = 3827.364870 (excluding connections establishing)
> 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.250000  | primary | primary | 33         | false             | 0                 |                   |                        | 2021-06-03 14:54:46
>  1       | /tmp     | 11003 | up     | up        | 0.250000  | standby | standby | 0          | false             | 13188720          | streaming         | async                  | 2021-06-03 14:54:46
>  2       | /tmp     | 11004 | up     | up        | 0.250000  | standby | standby | 45         | false             | 0                 | streaming         | async                  | 2021-06-03 14:54:46
>  3       | /tmp     | 11005 | up     | up        | 0.250000  | standby | standby | 24         | true              | 0                 | streaming         | async                  | 2021-06-03 14:54:46
> (4 rows)
> 
> I was expecting almost the same count of SELECTs were sent to node 2
> and 3. But in reality, about twice the number of SELECTs were sent to
> node 2. Shouldn't the same number of SELECTs be sent to node 2 and
> node 3 because replication delay of both nodes are equal (0)? Also
> this will be better from a point of performance view.

OK.
I will try modify the algorithm to select the load balancing node based on
backend_weight if there are multiple nodes with the lowest delay.


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