CODEWITHSUNDEEP
postgresqlpgpool
sabadow
sabadow

Reputation: 5153

Pgpool executes queries on standby nodes instead of master when replication is behind in standby

I have a postgresql 10 master db with 2 hot standby servers with streaming replication, and the replication is working correctly. The synchronous_commit is setted to remote_write

Also I have a pgpool 3.7.5 configured with the params:

delay_threshold = 1
sr_check_period = 1

And the the following weights:

In the log I can see the node1 and node2 are lagging:

Replication of node:1 is behind 75016 bytes from the primary server (node:0)

The pgpool docs says:

delay_threshold (integer)

Specifies the maximum tolerance level of replication delay in WAL bytes on the standby server against the primary server. If the delay exceeds this configured level, Pgpool-II stops sending the SELECT queries to the standby server and starts routing everything to the primary server even if load_balance_mode is enabled, until the standby catches-up with the primary. Setting this parameter to 0 disables the delay checking. This delay threshold check is performed every sr_check_period. Default is 0.

The problem it's that pgpool sends queries to the hot standbys before they obtained the new data from master through streaming replication.

I enabled the log_per_node_statement = on temporally to be able to see which node the query executes and I can see that queries are sent to the nodes even if there aren't sync when delay_threshold should avoid that.

Am I missing something? When the nodes are behind master the queries are not supposed to go the master?

Thanks in advance.

Other config values of pgpool are:

num_init_children = 120
max_pool = 3
connection_cache = off
load_balance_mode = on
master_slave_sub_mode = 'stream'
replication_mode = off
sr_check_period = 1

Upvotes: 3

Views: 2291

Answers (1)

jgj1018
jgj1018

Reputation: 67

first, I think you should check the result of "show pool_nodes" and check if three nodes are properly set with right role (primary, standby, standby).

second, did you set "app_name_redirect_preference_list" or "database_redirect_preference_list" ? If so, That can affect on selecting the node for SELECT query.

And in my opinion, I think delay_threshold = 1 is strict, the unit is bytes and in my case, I use "10000000" on PROD. why don't you just put "/NO LOAD BALANCE/" comment to send specific queries to only master?

And I simply recommend you to upgrade the version of pgpool to 4.0.0 (2018-10-19 released). 3.7.x has mysterious bug on load balancing.

I also faced a similar problem that load balancing is not working properly with the version (3.7.5) even when our configuration has no problem. The pgpool randomly We even contact pgpool developer team to solve this problem but they couldn't find the root cause. You can check the details in the link below.

https://www.pgpool.net/mantisbt/view.php?id=435.

And this was resolved like charm by upgrading to version 4.0.0.

Upvotes: 0

Related Questions