postgresql: Replaying of streaming WAL files is slow on some standby servers

Question

Environment: Postgresql 9.6.21. Multiple standby servers managed by patroni

+ Cluster: db11     (12345678901234567) ------+---------+-----+-----------+
| Member        | Host         | Role         | State   |  TL | Lag in MB |
+---------------+--------------+--------------+---------+-----+-----------+
| db11-01       | db11-01      | Leader       | running | 113 |           |
| db11-02       | db11-02      | Sync Standby | running | 113 |         0 |
| db11-03       | db11-03      | Sync Standby | running | 113 |         0 |
| db11-04       | db11-04      | Sync Standby | running | 113 |         8 |
+---------------+--------------+--------------+---------+-----+-----------+

Some of the pg_settings values:

          name               |              setting                                   
-----------------------------+-------------------------------------------------
 synchronous_commit          | on
 synchronous_standby_names   | 3 ("db11-02","db11-04","db11-03")
 max_standby_streaming_delay | 100
 hot_standby_feedback        | on

We have attempted with changing the number of synchronous standby from 1 to 3. Tried changing hot_standby_feedback to off.

Problem: The replay lag (precisely this - pg_xlog_location_diff(pg_last_xlog_receive_location(), pg_last_xlog_replay_location())) is varying on different standby servers. In the above setting, db11-04 has lowest replay lag, followed by db11-02 and db11-03 has the highest replay lag. In any different combination of standby servers.

There are no queries being issued on any of the standby servers (except queries doing the monitoring on all servers and continuous select queries on a small test table on db11-04 for monitoring again).

In any different combination of servers, db11-04 always has the lowest replication lag, db11-02 then comes next and db11-03 has the highest replication. This is a test environment and we can dispose of these servers, but would like to understand what causes some servers to behave differently.

Additional notes on the problem:

1. We tried modifying synchronous_standby_names with different order of the servers and different number of servers. Including the value "db11-04"
2. The servers db11-02 and db11-03 have been wiped clean, recreated the FS and replica from scratch -- assuming that there was a problem with FS defrag
3. The OS is Centos 7, the FS size (ext4) is 10TB and all servers are on AWS using r5d.4xlarge (16 CPUs, 128GB RAM)
4. The time drift on all the servers are well within a millisec.
5. The test setup involves creating a moderate write load, which generates 200+ WAL files per min.
6. The CPU load is very low, loadavg hovering just around 2 on all the standby servers. The disk IO is well within the limits.

The large replication lags on those couple of systems are causing issues for transactions using synchronous_commit as remote_apply

Question:

1. What would be causing different replay timings on different servers? This is a controlled test environment, and as I said, there are no real queries on the standby servers.
2. What parameters should we be looking at postgres level?

Update 1:

1. The pg_stat_database_conflicts shows 0 for all databases

Answer 1

I wouldn't call 200 WAL segments per minute (over 3 GB, if you have the default segment size of 16MB) "moderate". It may well be that the single thread that replays the WAL information is the bottleneck – but that wouldn't explain the discrepancy between the standby servers.

To debug that, set max_standby_streaming_delay to a low value like 0 or 1 and see if you get replication conflicts. If yes, that would point towards the most likely reason for the delay, and you would have to investigate differences in the workload of the standby servers.