5

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.

Replay lag ( received vs replayed

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
2
  • Just to clarify: the standbys aren't cascaded? Jun 30 at 8:11
  • No, they are not cascaded.
    – rsmoorthy
    Jun 30 at 10:22
0

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.

1
  • Thanks as always! I tried this max_standby_streaming_delay to 0, but did not see any benefit. There are 0 replication conflicts at all times. However, there are too many variations of replication lags across servers and the same servers having varying lag, across different runs, on a constant load. Right now, we are trying to recreate the problem outside of our environment. I will update more information once I am able to get them from the recreated env. Hope we will be able to get a better handle on replication lag variances soon.
    – rsmoorthy
    Jul 2 at 16:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.