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
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? |