Greetings, * Peter Eisentraut (peter.eisentr...@enterprisedb.com) wrote: > On 2021-01-13 23:10, Stephen Frost wrote: > >>Yes, I agree, and am involved in that thread as well- currently waiting > >>feedback from others about the proposed approach. > >I've tried to push that forward. I'm happy to update this patch once > >we've got agreement to move forward on that, to wit, adding to an > >'obsolete' section in the documentation information about this > >particular GUC and how it's been removed due to not being sensible or > >necessary to continue to have. > > Some discussion a few days ago was arguing that it was still necessary in > some cases as a way to counteract the possible lack of tuning in the kernel > flushing behavior. I think in light of that we should go with your first > patch that just changes the default, possibly with the documentation updated > a bit.
Rebased and updated patch attached which moves back to just changing the default instead of removing the option, with a more explicit call-out of the '90%', as suggested by Michael on the other patch. Any further comments or thoughts on this one? Thanks, Stephen
From 335b8e630fae6c229f27f70f85847e29dfc1b783 Mon Sep 17 00:00:00 2001 From: Stephen Frost <sfr...@snowman.net> Date: Tue, 19 Jan 2021 13:53:34 -0500 Subject: [PATCH] Change the default of checkpoint_completion_target to 0.9 Common recommendations are that the checkpoint should be spread out as much as possible, provided we avoid having it take too long. This change updates the default to 0.9 (from 0.5) to match that recommendation. There was some debate about possibly removing the option entirely but it seems there may be some corner-cases where having it set much lower to try to force the checkpoint to be as fast as possible could result in fewer periods of time of reduced performance due to kernel flushing. General agreement is that the "spread more" is the preferred approach though and those who need to tune away from that value are much less common. Reviewed-By: Michael Paquier, Peter Eisentraut Discussion: https://postgr.es/m/20201207175329.GM16415%40tamriel.snowman.net --- doc/src/sgml/config.sgml | 8 ++++- doc/src/sgml/wal.sgml | 29 ++++++++++++------- src/backend/utils/misc/guc.c | 2 +- src/backend/utils/misc/postgresql.conf.sample | 2 +- src/test/recovery/t/015_promotion_pages.pl | 1 - 5 files changed, 27 insertions(+), 15 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 82864bbb24..7e06d0febb 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -3266,7 +3266,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 66de1ee2f8..733eba22db 100644 --- a/doc/src/sgml/wal.sgml +++ b/doc/src/sgml/wal.sgml @@ -571,22 +571,29 @@ 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 (at around 90% of the last checkpoint's + duration). 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 17579eeaca..39d32542d2 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -3689,7 +3689,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 8930a94fff..4964134c8c 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; -- 2.25.1
signature.asc
Description: PGP signature
