Greetings, * Michael Paquier (mich...@paquier.xyz) wrote: > On Sun, Dec 06, 2020 at 10:03:08AM -0500, Stephen Frost wrote: > > * Alvaro Herrera (alvhe...@alvh.no-ip.org) wrote: > >> You keep making this statement, and I don't necessarily disagree, but if > >> that is the case, please explain why don't we have > >> checkpoint_completion_target set to 0.9 by default? Should we change > >> that? > > > > Yes, I do think we should change that.. > > Agreed. FWIW, no idea for others, but it is one of those parameters I > keep telling to update after a default installation.
Concretely, attached is a patch which changes the default and updates the documentation accordingly. Passes regression tests and doc build. Will register in the January commitfest as Needs Review. Thanks, Stephen
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 8cd3d6901c..47db860dd0 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -3260,7 +3260,13 @@ include_dir 'conf.d'
<listitem>
<para>
Specifies the target of checkpoint completion, as a fraction of
- total time between checkpoints. The default is 0.5.
+ total time between checkpoints. The default is 0.9, which spreads the
+ checkpoint across the entire checkpoint timeout period of time,
+ providing a consistent amount of I/O during the entire checkpoint.
+ Reducing this parameter is not recommended as that causes the I/O from
+ the checkpoint to have to complete faster, resulting in a higher I/O
+ rate, while then having a period of less I/O between the completion of
+ the checkpoint and the start of the next scheduled checkpoint.
This parameter can only be set in the <filename>postgresql.conf</filename>
file or on the server command line.
</para>
diff --git a/doc/src/sgml/wal.sgml b/doc/src/sgml/wal.sgml
index d1c3893b14..8ac3f971b7 100644
--- a/doc/src/sgml/wal.sgml
+++ b/doc/src/sgml/wal.sgml
@@ -524,22 +524,28 @@
writing dirty buffers during a checkpoint is spread over a period of time.
That period is controlled by
<xref linkend="guc-checkpoint-completion-target"/>, which is
- given as a fraction of the checkpoint interval.
+ given as a fraction of the checkpoint interval (configured by using
+ <varname>checkpoint_timeout</varname>).
The I/O rate is adjusted so that the checkpoint finishes when the
given fraction of
<varname>checkpoint_timeout</varname> seconds have elapsed, or before
<varname>max_wal_size</varname> is exceeded, whichever is sooner.
- With the default value of 0.5,
+ With the default value of 0.9,
<productname>PostgreSQL</productname> can be expected to complete each checkpoint
- in about half the time before the next checkpoint starts. On a system
- that's very close to maximum I/O throughput during normal operation,
- you might want to increase <varname>checkpoint_completion_target</varname>
- to reduce the I/O load from checkpoints. The disadvantage of this is that
- prolonging checkpoints affects recovery time, because more WAL segments
- will need to be kept around for possible use in recovery. Although
- <varname>checkpoint_completion_target</varname> can be set as high as 1.0,
- it is best to keep it less than that (perhaps 0.9 at most) since
- checkpoints include some other activities besides writing dirty buffers.
+ a bit before the next scheduled checkpoint. This spreads out the I/O as much as
+ possible to have the I/O load be consistent during the checkpoint. The
+ disadvantage of this is that prolonging checkpoints affects recovery time,
+ because more WAL segments will need to be kept around for possible use in recovery.
+ A user concerned about the amount of time required to recover might wish to reduce
+ <varname>checkpoint_timeout</varname>, causing checkpoints to happen more
+ frequently while still spreading out the I/O from each checkpoint. Alternatively,
+ <varname>checkpoint_completion_target</varname> could be reduced, but this would
+ result in times of more intense I/O (during the checkpoint) and times of less I/O
+ (after the checkpoint completed but before the next scheduled checkpoint) and
+ therefore is not recommended.
+ Although <varname>checkpoint_completion_target</varname> could be set as high as
+ 1.0, it is best to keep it less than that (such as at the default of 0.9, at most)
+ since checkpoints include some other activities besides writing dirty buffers.
A setting of 1.0 is quite likely to result in checkpoints not being
completed on time, which would result in performance loss due to
unexpected variation in the number of WAL segments needed.
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 635d91d50a..c0b2f99c11 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -3643,7 +3643,7 @@ static struct config_real ConfigureNamesReal[] =
NULL
},
&CheckPointCompletionTarget,
- 0.5, 0.0, 1.0,
+ 0.9, 0.0, 1.0,
NULL, NULL, NULL
},
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 9c9091e601..91d759dc61 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -230,7 +230,7 @@
#checkpoint_timeout = 5min # range 30s-1d
#max_wal_size = 1GB
#min_wal_size = 80MB
-#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
+#checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
#checkpoint_flush_after = 0 # measured in pages, 0 disables
#checkpoint_warning = 30s # 0 disables
diff --git a/src/test/recovery/t/015_promotion_pages.pl b/src/test/recovery/t/015_promotion_pages.pl
index 6fb70b5001..25a9e4764a 100644
--- a/src/test/recovery/t/015_promotion_pages.pl
+++ b/src/test/recovery/t/015_promotion_pages.pl
@@ -26,7 +26,6 @@ my $bravo = get_new_node('bravo');
$bravo->init_from_backup($alpha, 'bkp', has_streaming => 1);
$bravo->append_conf('postgresql.conf', <<EOF);
checkpoint_timeout=1h
-checkpoint_completion_target=0.9
EOF
$bravo->start;
signature.asc
Description: PGP signature
