pg_test_fsync -s 5
5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.
Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 92.364 ops/sec 10827 usecs/op
fdatasync 92.047 ops/sec 10864 usecs/op
fsync 45.893 ops/sec 21790 usecs/op
fsync_writethrough n/a
open_sync 45.849 ops/sec 21811 usecs/op
Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 33.290 ops/sec 30039 usecs/op
fdatasync 90.455 ops/sec 11055 usecs/op
fsync 45.314 ops/sec 22068 usecs/op
fsync_writethrough n/a
open_sync 22.740 ops/sec 43976 usecs/op
Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write
open_sync sizes.)
1 * 16kB open_sync write 45.280 ops/sec 22085 usecs/op
2 * 8kB open_sync writes 20.371 ops/sec 49088 usecs/op
4 * 4kB open_sync writes 11.346 ops/sec 88140 usecs/op
8 * 2kB open_sync writes 5.334 ops/sec 187477 usecs/op
16 * 1kB open_sync writes 2.671 ops/sec 374454 usecs/op
Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written on a different
descriptor.)
write, fsync, close 45.814 ops/sec 21828 usecs/op
write, close, fsync 41.236 ops/sec 24251 usecs/op
Non-sync'ed 8kB writes:
write 202147.625 ops/sec 5 usecs/op
pg_test_fsync -s 5
5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.
Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 28146.149 ops/sec 36 usecs/op
fdatasync 26721.581 ops/sec 37 usecs/op
fsync 23500.918 ops/sec 43 usecs/op
fsync_writethrough n/a
open_sync 25170.058 ops/sec 40 usecs/op
Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 14390.340 ops/sec 69 usecs/op
fdatasync 19655.191 ops/sec 51 usecs/op
fsync 17476.174 ops/sec 57 usecs/op
fsync_writethrough n/a
open_sync 12840.007 ops/sec 78 usecs/op
Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write
open_sync sizes.)
1 * 16kB open_sync write 19287.311 ops/sec 52 usecs/op
2 * 8kB open_sync writes 12786.778 ops/sec 78 usecs/op
4 * 4kB open_sync writes 7527.042 ops/sec 133 usecs/op
8 * 2kB open_sync writes write failed: Invalid argument
The speed is going to be governed by the available hardware, so improve the hardware to improve the speed of the initial data copy. You should also be able to add space to the file system for your wal logs without too much difficulty in RHEL7.On Wed, Sep 29, 2021 at 9:26 AM Nikhil Shetty <nikhil.dba04@xxxxxxxxx> wrote:Hi Team,We have a 10 TB database most of which is occupied by a single table. This table is around 9.5TB. Bloat is 3.5TB for this table. So, the total table size without bloat is 6TB.We are using native logical replication to remove the bloat for this table to another instance but it takes more than a day to copy 1.5TB of data. With this speed it will take 4-5 days for the initial sync itself. During this initial sync, the replication slot holds WAL and creates disk usage problems on the primary, so we had to stop the replication. We cannot remove the PK since it is the Replica identity.Other indexes and FK have been removed from the destination(secondary).Other changes we have made in the destination(secondary) instance but still no luck- fsync=off,synchronous_commit=off, archive_mode=off.Since it is a single table, we cannot increase worker_processes. Source database is highly transactional and we do not need any downtime.Is there any other option to increase the speed of initial data copy in logical replication?Postgres Version - 11.7OS - OEL7Thanks and Regards,Nikhil
--Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
