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 1lOjps-0005rB-7b for pgsql-hackers@arkaria.postgresql.org; Tue, 23 Mar 2021 16:24:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lOjpr-0000MX-56 for pgsql-hackers@arkaria.postgresql.org; Tue, 23 Mar 2021 16:24:51 +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 1lOjpq-0000MQ-7M for pgsql-hackers@lists.postgresql.org; Tue, 23 Mar 2021 16:24:50 +0000 Received: from tamriel.snowman.net ([2001:470:e38f::11]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lOjpn-0004Gf-Ic for pgsql-hackers@postgresql.org; Tue, 23 Mar 2021 16:24:49 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id A13375F799; Tue, 23 Mar 2021 12:24:46 -0400 (EDT) Date: Tue, 23 Mar 2021 12:24:46 -0400 From: Stephen Frost To: Michael Paquier Cc: David Steele , Peter Eisentraut , Alvaro Herrera , Laurenz Albe , "Bossart, Nathan" , Bernd Helmle , "tsunakawa.takay@fujitsu.com" , "pgsql-hackers@postgresql.org" , Tom Lane Subject: Re: Change default of checkpoint_completion_target Message-ID: <20210323162446.GL20766@tamriel.snowman.net> References: <20201210172126.GA25979@alvherre.pgsql> <20201210172255.GQ16415@tamriel.snowman.net> <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> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="xqHVPYWunkI++waB" 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 --xqHVPYWunkI++waB Content-Type: multipart/mixed; boundary="izVXBNjbQxLAL8fp" Content-Disposition: inline --izVXBNjbQxLAL8fp Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, * Michael Paquier (michael@paquier.xyz) wrote: > On Mon, Mar 22, 2021 at 01:11:00PM -0400, Stephen Frost wrote: > > Unless there's anything further on this, I'll plan to commit it tomorrow > > or Wednesday. >=20 > Cool, looks fine to me. >=20 > This version of the patch has forgotten to update one spot: > src/backend/postmaster/checkpointer.c:double CheckPointCompletionTarget = =3D 0.5; Hah! Indeed! Fixed in the attached. Thanks! Stephen --izVXBNjbQxLAL8fp Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="cct_def_v6.patch" Content-Transfer-Encoding: quoted-printable =46rom 1c69cca6fc9bbd921f873cb208ffcdbd68bde586 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 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..44763f0180 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 a= s that + causes the I/O from the checkpoint to have to complete faster, res= ulting + in a higher I/O rate, while then having a period of less I/O betwe= en the + completion of the checkpoint and the start of 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..4354051c7b 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 to have the I/= O load be + consistent during the checkpoint. The disadvantage of this is that pro= longing + 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 checkpoint_timeout, + causing checkpoints to happen more frequently while still spreading out= the I/O + from each checkpoint. 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 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 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 --izVXBNjbQxLAL8fp-- --xqHVPYWunkI++waB Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBCgAGBQJgWhZOAAoJEO1sijiDR2RVlvMQAKAQeEJesxk1oe8sUpRix/vj 9BHJQpKf3bvDMIga5bCrGRFztK9mdSfsIxIkSP2Icoz8moFceySEH81LvJVcUKJW mnldkZHBnXjAYyhqIj6sBj8s/Q8AbrbFglycgE/XoveA1wMcZTjQbFTn0auGypT2 jh26FqsATnOhn0GXLPj2A8tih/+K19ZpvI7YtVxaLjEF9ydvHk3S0duuETY/LJDP 0E0EG3MTq0Jtv/GyXnfUhKPVp3a/jTf4bOGy8hLXEYUQgcPObtbJu6nnwQzziocj H+EW70/vb8XHLuWn+6Lg/2YPtPwRNRyi5vsgzzGe+9DqA97J7a3S97kJepBqtXv7 P/CeUzOAePSzZ1JYnqhmUv1irvdivAsXUr0xA2XaMii+fnOXlbVkyLxp5JuX3lWJ aCWfQD+N27TLaKTwA5e/8BjNqoFqOEDx/NZDXfWC9XCAjRtqYRazp/jAFO9gTHkP /DbTb6ljYYbzs8thEkYzIbzin6xbprVWdT+PpgzW3NMYHN5GxkEll8hVjF5WnxJk 4Yi1h2Tb6SlgpxIC5kdLOB8Zyb4JUaW8GbNhTpo5r0a6419e3dpWA0JnMFfiS/Ra ncG23zN0pjwvYdmppoCsaQJT1qmdDTIVrUq62nBfIQxQZ2Iji7CNOIz15cIhzj51 WEc4GcLwX5nRkSELMNsm =BQ0C -----END PGP SIGNATURE----- --xqHVPYWunkI++waB--