Search Postgresql Archives

Logical Replication Delay

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi Team,

We have configured bidirectional replication (but traffic can only flow in one direction) between two data centers (distance: 1000 km, maximum Network latency: 100 ms) with an application TPS (transactions per second) of 700 at maximum.

We are fine with handling up to 500 TPS without observing any lag between the two data centers. However, when TPS increases, we notice a lag in WAL files of over 100 GB (initially, it was 1 TB, but after tuning, it was reduced to 100 GB). During peak times, WAL files are generated at a rate of 4 GB per minute.

All transactions (Tx) take less than 200 ms, with a maximum of 1 second at times (no long-running transactions).

Here are the configured parameters and resources:

Parameters configured on both sides:

Name                                                              Setting         Unit


log_replication_commandsoff
logical_decoding_work_mem524288kB
max_logical_replication_workers16
max_parallel_apply_workers_per_subscription 2
max_replication_slots20
max_sync_workers_per_subscription2
max_wal_senders20
max_worker_processes40
wal_levellogical
wal_receiver_timeout600000ms
wal_segment_size1073741824B
wal_sender_timeout600000ms

Optimizations applied:

  1. Vacuum freeze is managed during off-hours; no aggressive vacuum is triggered during business hours.
  2. Converted a few tables to unlogged.
  3. Removed unwanted tables from publication.
  4. Partitioned all large tables.

Pending:

  1. Turning off/tuning autovacuum parameters to avoid triggering during business hours.

Not possible: We are running all tables in a single publication, and it is not possible to separate them.

I would greatly appreciate any suggestions you may have to help avoid logical replication delays, whether through tuning database or operating system parameters, or any other recommendations


--
Thanks & Regards,
Ram.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux