Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lOmYZ-0003wA-Cd for pgsql-hackers@arkaria.postgresql.org; Tue, 23 Mar 2021 19:19:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lOmYY-0005af-3F for pgsql-hackers@arkaria.postgresql.org; Tue, 23 Mar 2021 19:19:10 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lOmYX-0005aY-NY for pgsql-hackers@lists.postgresql.org; Tue, 23 Mar 2021 19:19:09 +0000 Received: from tamriel.snowman.net ([70.109.60.50]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lOmYV-0005gk-2f for pgsql-hackers@postgresql.org; Tue, 23 Mar 2021 19:19:08 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id B16A35F799; Tue, 23 Mar 2021 15:19:06 -0400 (EDT) Date: Tue, 23 Mar 2021 15:19:06 -0400 From: Stephen Frost To: "Bossart, Nathan" Cc: Michael Paquier , David Steele , Peter Eisentraut , Alvaro Herrera , Laurenz Albe , Bernd Helmle , "tsunakawa.takay@fujitsu.com" , "pgsql-hackers@postgresql.org" , Tom Lane Subject: Re: Change default of checkpoint_completion_target Message-ID: <20210323191906.GO20766@tamriel.snowman.net> References: <20210113221038.GG27507@tamriel.snowman.net> <20210119191450.GO27507@tamriel.snowman.net> <519044.1611084646@sss.pgh.pa.us> <20210119194748.GP27507@tamriel.snowman.net> <20210322171059.GB20766@tamriel.snowman.net> <20210323162446.GL20766@tamriel.snowman.net> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="KEg0ueogd88PzVDf" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.24 (2015-08-30) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --KEg0ueogd88PzVDf Content-Type: multipart/mixed; boundary="Ve4cA8BWCCk2ygeA" Content-Disposition: inline --Ve4cA8BWCCk2ygeA Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Greetings, * Bossart, Nathan (bossartn@amazon.com) wrote: > LGTM. I just have a few small wording suggestions. Agreed, those looked like good suggestions and so I've incorporated them. Updated patch attached. Thanks! Stephen --Ve4cA8BWCCk2ygeA Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="cct_def_v7.patch" Content-Transfer-Encoding: quoted-printable =46rom 40a529bc0a129e90c9917c1a3df2297ac7f2e073 Mon Sep 17 00:00:00 2001 =46rom: Stephen Frost Date: Tue, 19 Jan 2021 13:53:34 -0500 Subject: [PATCH] Change checkpoint_completion_target default 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, Tom Lane, David Steele, Nathan Bossart Discussion: https://postgr.es/m/20201207175329.GM16415%40tamriel.snowman.net --- doc/src/sgml/config.sgml | 12 ++++++-- doc/src/sgml/wal.sgml | 29 ++++++++++++------- src/backend/postmaster/checkpointer.c | 2 +- src/backend/utils/misc/guc.c | 2 +- src/backend/utils/misc/postgresql.conf.sample | 2 +- src/test/recovery/t/015_promotion_pages.pl | 1 - 6 files changed, 30 insertions(+), 18 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 5679b40dd5..0d101f65f6 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -3302,9 +3302,15 @@ include_dir 'conf.d' Specifies the target of checkpoint completion, as a fraction of - total time between checkpoints. The default is 0.5. - This parameter can only be set in the postgresql.conf - file or on the server command line. + total time between checkpoints. The default is 0.9, which spreads = the + checkpoint across almost all of the available interval, providing = fairly + consistent I/O load while also leaving some time for checkpoint + completion overhead. Reducing this parameter is not recommended b= ecause + it causes the checkpoint to complete faster. This results in a hi= gher + rate of I/O during the checkpoint followed by a period of less I/O= between + the checkpoint completion and the next scheduled checkpoint. This + parameter can only be set in the postgresql.conf file + or on the server command line. diff --git a/doc/src/sgml/wal.sgml b/doc/src/sgml/wal.sgml index ae4a3c1293..7d48f42710 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 ti= me. That period is controlled by , which is - given as a fraction of the checkpoint interval. + given as a fraction of the checkpoint interval (configured by using + checkpoint_timeout). The I/O rate is adjusted so that the checkpoint finishes when the given fraction of checkpoint_timeout seconds have elapsed, or before max_wal_size is exceeded, whichever is sooner. - With the default value of 0.5, + With the default value of 0.9, PostgreSQL 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 checkpoint_completion_target - to reduce the I/O load from checkpoints. The disadvantage of this is t= hat - prolonging checkpoints affects recovery time, because more WAL segments - will need to be kept around for possible use in recovery. Although - checkpoint_completion_target can be set as high as 1= =2E0, - 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 c= heckpoint's + duration). This spreads out the I/O as much as possible so that the ch= eckpoint + I/O load is consistent throughout the checkpoint interval. The disadva= ntage 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 u= ser + concerned about the amount of time required to recover might wish to re= duce + checkpoint_timeout so that checkpoints occur more fr= equently + but still spread the I/O across the checkpoint interval. Alternatively, + checkpoint_completion_target could be reduced, but t= his would + result in times of more intense I/O (during the checkpoint) and times o= f less I/O + (after the checkpoint completed but before the next scheduled checkpoin= t) and + therefore is not recommended. + Although checkpoint_completion_target could be set a= s high as + 1.0, it is typically recommended to set it to no higher than 0.9 (the d= efault) + since checkpoints include some other activities besides writing dirty b= uffers. 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/postmaster/checkpointer.c b/src/backend/postmaster= /checkpointer.c index 5907a7befc..e7e6a2a459 100644 --- a/src/backend/postmaster/checkpointer.c +++ b/src/backend/postmaster/checkpointer.c @@ -145,7 +145,7 @@ static CheckpointerShmemStruct *CheckpointerShmem; */ int CheckPointTimeout =3D 300; int CheckPointWarning =3D 30; -double CheckPointCompletionTarget =3D 0.5; +double CheckPointCompletionTarget =3D 0.9; =20 /* * Private state diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 3b36a31a47..5fe7f2fa01 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -3727,7 +3727,7 @@ static struct config_real ConfigureNamesReal[] =3D NULL }, &CheckPointCompletionTarget, - 0.5, 0.0, 1.0, + 0.9, 0.0, 1.0, NULL, NULL, NULL }, =20 diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/ut= ils/misc/postgresql.conf.sample index 86425965d0..68f95bdfff 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -231,7 +231,7 @@ #checkpoint_timeout =3D 5min # range 30s-1d #max_wal_size =3D 1GB #min_wal_size =3D 80MB -#checkpoint_completion_target =3D 0.5 # checkpoint target duration, 0.0 - = 1.0 +#checkpoint_completion_target =3D 0.9 # checkpoint target duration, 0.0 - = 1.0 #checkpoint_flush_after =3D 0 # measured in pages, 0 disables #checkpoint_warning =3D 30s # 0 disables =20 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 =3D get_new_node('bravo'); $bravo->init_from_backup($alpha, 'bkp', has_streaming =3D> 1); $bravo->append_conf('postgresql.conf', <start; =20 --=20 2.27.0 --Ve4cA8BWCCk2ygeA-- --KEg0ueogd88PzVDf Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBCgAGBQJgWj8qAAoJEO1sijiDR2RVke4P/AwMvFobKb8jpP9hCylj9c4S 3P+hzCIGTwlN4ci1/PIlTC2f4QOyYgHlKwk+vpqIkC0NDs+vcjB0fqRxE6hvOm83 3J38/8Mt3CH6quQxIjBHjxVhP12RtUyLpvkVXsRjOYpPNr0nnYnThP18ulu36Wx0 pyq4fcO64/yp2D/GdBiVBU7Uguu7HNL3N2Zw8DDdu3K8EovB+Wdh40KM4fMnx0Ns HKUUqueaDHs2/wZOsIYQJ3ilsT9PtGTBGCQ92oN/DKp8HbOejmPLDlGZUlWXfHAp l6x5GWaiWTGHlY0aN5A2BMfSk1zF0ETjon3TxN6ljiWH+moQrI2wcW+dTqSYj7pG rQpGeWIDmRl1F6icZrwd3cD6kj+uG/MDt/JGTJCxjnpA6fKKPRE/JWrfhNvZpd6T ADTnnlp7IHSVutqlv/DN+GBIa5vbAzdD78Qxr7TbcuBtxocIvm7mhrdoCHgusOMZ CnYXc0xMvufvvNyFumrVvtwbnBbd4tLcVcMJmu1ddRpIYWtU0v+jtrL7oV3JOGJN 1a08z7zTx8TA9QTnkXAcbeTs7zKsBDFdqQZBwVXp4snxU2bESCii5mZhO3b+fDoy u3TlFZWRD1+6m2luBjomCokbLmodEVSSj6goEC8lL4xLUiunB6+m/OZ0XKg5hU/w jcDkLMm1dSZfaRCiasJ1 =z55M -----END PGP SIGNATURE----- --KEg0ueogd88PzVDf--