Thread: [GENERAL] wal configuration setting for fast streaming replication with logicaldecoding
[GENERAL] wal configuration setting for fast streaming replication with logicaldecoding
From
Weiping Qu
Date:
Hi, I intend to increase the speed of streaming replication with logical decoding using following configuration: wal_level = logical fsync = on synchronous_commit = off wal_sync_method = fdatasync wal_buffers = 256MB wal_writer_delay = 2seconds checkpoint_timeout = 15min max_wal_size=10GB The intention is to first let WAL records to be buffered in WAL buffers (with increasing wal_buffers as 256MB) by turning off synchronous_commit and increasing the wal_writer_delay to 2 second. Target WAL records are wished to be directly fetched from RAM through streaming replication to external nodes, thus reducing I/Os. Besides, to avoid expensive checkpoints, its timeout and max_wal_size are also increased. However, as suggested online, wal_buffers should be not more than one WAL segment file which is 16MB. and wal_writer_delay should be at millisecond level. Therefore, I would like to listen to your opinions. Besides, I would also like to fetch WAL records periodically (say per 150 ms) which would cause pile-up of WAL records in memory at each wal_writer_delay interval. As also introduced online, when XLogInsertRecord is called, a new record is inserted in to WAL buffers, if no space, then a few WAL records would be moved to kernel cache (buffer cache). Shall I also set vm.dirty_background_ratio = 5 and vm.dirty_ratio = 80 to avoid disk I/Os? Looking forward to your kind help. Best, Weiping -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general