public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ramakrishna m <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: Logical Replication Delay
Date: Sun, 22 Sep 2024 00:38:02 +0530
Message-ID: <CAG-eXHKr5pFzCKNqVhjQAUM0oxxCe8YUfjoLT8WmZx54Sk48kg@mail.gmail.com> (raw)
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:*
- *OS*: Ubuntu
- *RAM*: 376 GB
- *CPU*: 64 cores
- *Swap*: 32 GB
- *PostgreSQL Version*: 16.4 (each side has 3 nodes with Patroni and
etcd configured)
- *DB Size*: 15 TB
*Parameters configured on both sides:*
Name Setting
Unit
log_replication_commands off
logical_decoding_work_mem 524288 kB
max_logical_replication_workers 16
max_parallel_apply_workers_per_subscription 2
max_replication_slots 20
max_sync_workers_per_subscription 2
max_wal_senders 20
max_worker_processes 40
wal_level logical
wal_receiver_timeout 600000 ms
wal_segment_size 1073741824 B
wal_sender_timeout 600000 ms
*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.
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Logical Replication Delay
In-Reply-To: <CAG-eXHKr5pFzCKNqVhjQAUM0oxxCe8YUfjoLT8WmZx54Sk48kg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox