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

[Tatsuo Ishii]

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

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