Bulk operations like CREATE INDEX, ALTER TABLE, or bulk loads can create
a lot of WAL. A lot of WAL at once can cause delays in replication.
For synchronous replication, this can make seemingly unrelated sessions
hang. But also for asynchronous replication, it will increase latency.
One idea to address this is to slow down WAL-generating maintenance
operations. This is similar to the vacuum delay. Where the vacuum
delay counts notional I/O cost before sleeping, here we would count how
much WAL has been generated and sleep after some amount.
I attach an example patch for this functionality. It introduces three
settings:
wal_insert_delay_enabled
wal_insert_delay
wal_insert_delay_size
When you turn on wal_insert_delay_enabled, then it will sleep for
wal_insert_delay after the session has produced wal_insert_delay_size of
WAL data.
The idea is that you would tune wal_insert_delay and
wal_insert_delay_size to your required performance characteristics and
then turn on wal_insert_delay_enabled individually in maintenance jobs
or similar.
To test, for example, set up pgbench with synchronous replication and
run an unrelated large index build in a separate session. With the
settings, you can make it as fast or as slow as you want.
Tuning these settings, however, is quite mysterious I fear. You have to
play around a lot to get settings that achieve the right balance.
So, some questions:
Is this useful?
Any other thoughts on how to configure this or do this?
Should we aim for a more general delay system, possibly including vacuum
delay and perhaps something else?
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From d025e57adf6f018e615cca113294bf4c3133ebe6 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Mon, 11 Feb 2019 11:07:40 +0100
Subject: [PATCH] wal_insert_delay configuration settings
---
doc/src/sgml/config.sgml | 52 +++++++++++++++++++
src/backend/access/transam/xlog.c | 19 +++++++
src/backend/utils/misc/guc.c | 32 ++++++++++++
src/backend/utils/misc/postgresql.conf.sample | 4 ++
src/include/access/xlog.h | 3 ++
5 files changed, 110 insertions(+)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 7e208a4b81..a4a1525453 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -2829,6 +2829,58 @@ <title>Settings</title>
</listitem>
</varlistentry>
+ <varlistentry id="guc-wal-insert-delay-enabled"
xreflabel="wal_insert_delay_enabled">
+ <term><varname>wal_insert_delay_enabled</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>wal_insert_delay_enabled</varname> configuration
parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ When this setting is turned on, a session sleeps a time of <xref
+ linkend="guc-wal-insert-delay"/> after WAL data of size at least <xref
+ linkend="guc-wal-insert-delay-size"/> has been generated. The default
+ is off.
+ </para>
+
+ <para>
+ The purpose of this setting is to slow down maintenance operations, in
+ particular to avoid that maintenance operations cause replication lag
+ by writing WAL too quickly. It is not sensible to enable this setting
+ globally.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-wal-insert-delay" xreflabel="wal_insert_delay">
+ <term><varname>wal_insert_delay</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>wal_insert_delay</varname> configuration
parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Sets the length of the sleep described under <xref
+ linkend="guc-wal-insert-delay-enabled"/>. The default is 100
+ milliseconds (but the overall feature is off by default).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-wal-insert-delay-size"
xreflabel="wal_insert_delay_size">
+ <term><varname>wal_insert_delay_size</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>wal_insert_delay_size</varname> configuration
parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Sets the size of WAL inserted to trigger the sleep described under
+ <xref linkend="guc-wal-insert-delay-enabled"/>. The default is 1 MB
+ (but the overall feature is off by default).
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</sect2>
<sect2 id="runtime-config-wal-checkpoints">
diff --git a/src/backend/access/transam/xlog.c
b/src/backend/access/transam/xlog.c
index a9f3272849..3298e258c7 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -100,6 +100,9 @@ int wal_level = WAL_LEVEL_MINIMAL;
int CommitDelay = 0; /* precommit delay in
microseconds */
int CommitSiblings = 5; /* # concurrent xacts needed to
sleep */
int wal_retrieve_retry_interval = 5000;
+bool wal_insert_delay_enabled = false;
+int wal_insert_delay;
+int wal_insert_delay_size;
#ifdef WAL_DEBUG
bool XLOG_DEBUG = false;
@@ -1220,6 +1223,22 @@ XLogInsertRecord(XLogRecData *rdata,
}
#endif
+ /*
+ * Delay if requested
+ */
+ if (wal_insert_delay_enabled)
+ {
+ static int64 wal_insert_balance = 0;
+
+ wal_insert_balance += (EndPos - StartPos);
+
+ while (wal_insert_balance > wal_insert_delay_size)
+ {
+ pg_usleep(wal_insert_delay * 1000L);
+ wal_insert_balance -= wal_insert_delay_size;
+ }
+ }
+
/*
* Update our global variables
*/
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index ea5444c6f1..ef8b1fd0e2 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -1176,6 +1176,16 @@ static struct config_bool ConfigureNamesBool[] =
NULL, NULL, NULL
},
+ {
+ {"wal_insert_delay_enabled", PGC_USERSET, WAL_SETTINGS,
+ gettext_noop("Sleeps after inserting a certain amount
of WAL."),
+ NULL
+ },
+ &wal_insert_delay_enabled,
+ false,
+ NULL, NULL, NULL
+ },
+
{
{"log_checkpoints", PGC_SIGHUP, LOGGING_WHAT,
gettext_noop("Logs each checkpoint."),
@@ -2079,6 +2089,28 @@ static struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"wal_insert_delay", PGC_USERSET, WAL_SETTINGS,
+ gettext_noop("Sets the sleep time after inserting a
certain amount of WAL."),
+ NULL,
+ GUC_UNIT_MS
+ },
+ &wal_insert_delay,
+ 100, 0, INT_MAX,
+ NULL, NULL, NULL
+ },
+
+ {
+ {"wal_insert_delay_size", PGC_USERSET, WAL_SETTINGS,
+ gettext_noop("Sets the amount of WAL after which to
sleep."),
+ NULL,
+ GUC_UNIT_BYTE
+ },
+ &wal_insert_delay_size,
+ 1024 * 1024, 0, INT_MAX,
+ NULL, NULL, NULL
+ },
+
{
{"max_connections", PGC_POSTMASTER, CONN_AUTH_SETTINGS,
gettext_noop("Sets the maximum number of concurrent
connections."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample
b/src/backend/utils/misc/postgresql.conf.sample
index ad6c436f93..62d75da3c7 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -214,6 +214,10 @@
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
+#wal_insert_delay_enabled = off
+#wal_insert_delay = 100ms
+#wal_insert_delay_size = 1MB
+
# - Checkpoints -
#checkpoint_timeout = 5min # range 30s-1d
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index f90a6a9139..c66a77b18e 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -127,6 +127,9 @@ extern int recoveryTargetAction;
extern int recovery_min_apply_delay;
extern char *PrimaryConnInfo;
extern char *PrimarySlotName;
+extern bool wal_insert_delay_enabled;
+extern int wal_insert_delay;
+extern int wal_insert_delay_size;
/* indirectly set via GUC system */
extern TransactionId recoveryTargetXid;
--
2.20.1
