From 7975c5e0a992ae9a45e03d145e0d37e2b5a707f5 Mon Sep 17 00:00:00 2001 From: Andres Freund Date: Mon, 15 Feb 2016 23:52:38 +0100 Subject: Allow the WAL writer to flush WAL at a reduced rate. Commit 4de82f7d7 increased the WAL flush rate, mainly to increase the likelihood that hint bits can be set quickly. More quickly set hint bits can reduce contention around the clog et al. But unfortunately the increased flush rate can have a significant negative performance impact, I have measured up to a factor of ~4. The reason for this slowdown is that if there are independent writes to the underlying devices, for example because shared buffers is a lot smaller than the hot data set, or because a checkpoint is ongoing, the fdatasync() calls force cache flushes to be emitted to the storage. This is achieved by flushing WAL only if the last flush was longer than wal_writer_delay ago, or if more than wal_writer_flush_after (new GUC) unflushed blocks are pending. Based on some tests the default for wal_writer_delay is 1MB, which seems to work well both on SSD and rotational media. To avoid negative performance impact due to 4de82f7d7 an earlier commit (db76b1e) made SetHintBits() more likely to succeed; preventing performance regressions in the pgbench tests I performed. Discussion: 20160118163908.GW10941@awork2.anarazel.de --- doc/src/sgml/config.sgml | 41 ++++++++++++++++++++++++++++++++--------- 1 file changed, 32 insertions(+), 9 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index de84b777300..a09ceb2fea7 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -2344,15 +2344,38 @@ include_dir 'conf.d' - - Specifies the delay between activity rounds for the WAL writer. - In each round the writer will flush WAL to disk. It then sleeps for - wal_writer_delay milliseconds, and repeats. The default - value is 200 milliseconds (200ms). Note that on many - systems, the effective resolution of sleep delays is 10 milliseconds; - setting wal_writer_delay to a value that is not a multiple - of 10 might have the same results as setting it to the next higher - multiple of 10. This parameter can only be set in the + + Specifies how often the WAL writer flushes WAL. After flushing WAL it + sleeps for wal_writer_delay milliseconds, unless woken up + by an asynchronously committing transaction. In case the last flush + happened less than wal_writer_delay milliseconds ago and + less than wal_writer_flush_after bytes of WAL have been + produced since, WAL is only written to the OS, not flushed to disk. + The default value is 200 milliseconds (200ms). Note that + on many systems, the effective resolution of sleep delays is 10 + milliseconds; setting wal_writer_delay to a value that is + not a multiple of 10 might have the same results as setting it to the + next higher multiple of 10. This parameter can only be set in the + postgresql.conf file or on the server command line. + + + + + + wal_writer_flush_after (integer) + + wal_writer_flush_after configuration parameter + + + + + Specifies how often the WAL writer flushes WAL. In case the last flush + happened less than wal_writer_delay milliseconds ago and + less than wal_writer_flush_after bytes of WAL have been + produced since, WAL is only written to the OS, not flushed to disk. + If wal_writer_flush_after is set to 0 WAL is + flushed everytime the WAL writer has written WAL. The default is + 1MB. This parameter can only be set in the postgresql.conf file or on the server command line. -- cgit v1.2.3