wal configuration setting for fast streaming replication with logical decoding

From: Weiping Qu <qu(at)informatik(dot)uni-kl(dot)de>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: wal configuration setting for fast streaming replication with logical decoding
Date: 2017-11-08 20:52:44
Message-ID: 1b3af0ec-9c12-547e-c16a-b67bb1d080b0@informatik.uni-kl.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2017-11-08 21:34:04 Re: Because PostgreSQL is compiling in old versions of OS?
Previous Message José María Terry Jiménez 2017-11-08 20:11:54 Re: Because PostgreSQL is compiling in old versions of OS?