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 1l1wyp-0002c5-5O for pgsql-hackers@arkaria.postgresql.org; Tue, 19 Jan 2021 19:47:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l1wyo-0004Cy-3K for pgsql-hackers@arkaria.postgresql.org; Tue, 19 Jan 2021 19:47:54 +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 1l1wyn-0004Cd-O6 for pgsql-hackers@lists.postgresql.org; Tue, 19 Jan 2021 19:47:53 +0000 Received: from tamriel.snowman.net ([2001:470:e38f::11]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l1wyk-0005DT-Sa for pgsql-hackers@postgresql.org; Tue, 19 Jan 2021 19:47:52 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id A65715F799; Tue, 19 Jan 2021 14:47:48 -0500 (EST) Date: Tue, 19 Jan 2021 14:47:48 -0500 From: Stephen Frost To: Tom Lane Cc: Peter Eisentraut , Alvaro Herrera , Laurenz Albe , "Bossart, Nathan" , Michael Paquier , Bernd Helmle , "tsunakawa.takay@fujitsu.com" , "pgsql-hackers@postgresql.org" Subject: Re: Change default of checkpoint_completion_target Message-ID: <20210119194748.GP27507@tamriel.snowman.net> References: <20201210171602.GP16415@tamriel.snowman.net> <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> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="M5bOZW4AjlnlH/us" Content-Disposition: inline In-Reply-To: <519044.1611084646@sss.pgh.pa.us> User-Agent: Mutt/1.5.24 (2015-08-30) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --M5bOZW4AjlnlH/us Content-Type: multipart/mixed; boundary="Myl1Jfsu5DOvTUHt" Content-Disposition: inline --Myl1Jfsu5DOvTUHt Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, * Tom Lane (tgl@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > Any further comments or thoughts on this one? >=20 > This: >=20 > + total time between checkpoints. The default is 0.9, which spread= s the > + checkpoint across the entire checkpoint timeout period of time, >=20 > is confusing because 0.9 is obviously not 1.0; people will wonder > whether the scale is something strange or the text is just wrong. > They will also wonder why not use 1.0 instead. So perhaps more like >=20 > ... The default is 0.9, which spreads the checkpoint across almost > all the available interval, providing fairly consistent I/O load > while also leaving some slop for checkpoint completion overhead. >=20 > The other chunk of text seems accurate, but there's no reason to let > this one be misleading. Good point, updated along those lines. In passing, I noticed that we have a lot of documentation like: This parameter can only be set in the postgresql.conf file or on the server command line. =2E.. which hasn't been true since the introduction of ALTER SYSTEM. I don't really think it's this patch's job to clean that up but it doesn't seem quite right that we don't include ALTER SYSTEM in that list either. If this was C code, maybe we could get away with just changing such references as we find them, but I don't think we'd want the documentation to be in an inconsistent state regarding that. Anyone want to opine about what to do with that? Should we consider changing those to mention ALTER SYSTEM? Or perhaps have a way of saying "at server start" that then links to "how to set options at server start", perhaps.. Thanks, Stephen --Myl1Jfsu5DOvTUHt Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="cct_def_v4.patch" Content-Transfer-Encoding: quoted-printable =46rom 97c24d92e4ae470a257aa2ac9501032aba5edd82 Mon Sep 17 00:00:00 2001 =46rom: Stephen Frost 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, Tom Lane Discussion: https://postgr.es/m/20201207175329.GM16415%40tamriel.snowman.net --- doc/src/sgml/config.sgml | 12 ++++++-- 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, 29 insertions(+), 17 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 82864bbb24..666b467eda 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -3266,9 +3266,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 slop 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 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 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/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[] =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 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 =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.25.1 --Myl1Jfsu5DOvTUHt-- --M5bOZW4AjlnlH/us Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBCgAGBQJgBzdkAAoJEO1sijiDR2RV8wIP/2hXeh9YtQO37qVOYlLnJ/xW T1uAMw+lYySWMRA9zQEtfpcqhK61eMn95UmnLuhdAj5FLE9VQ/N4+eEJlCksCese HqbeZnIPBcXMseH4DBBBpiiiMMdKqkKPm/W4FYfJvTlsBoWTWaL6N+XFf+5MTyHc Pi42YpCdE6lNNn0oZSz3UuK557+/N0Ee9WRRsIn39h5nzM57C5SiyQuzWYoYVZ30 nrShPsEY0+ed4KEdZR37+rnLa/Ss9e02r9XwYHqVCIW1jdkcWN3fetKfXkdf4fYe hK4h3iWLQEaFQtpUvYCD3yRgzrG+MbRJjMjU7UPDH7Vj1hipyCF7KtrCp8QmFNBa vPWukwLegi5vDZZRraBxQwHKrMUrJfR30SIKVkUPO7E/wfusoWF+t3xGxevufZkX iINMBpkxSgwaKwb/GHBAWIO/TWORlxS3bePffAoYKYA16hkINVPC/UYceHfctW8A h7y4Vk8CSfbYUb26nIzDA4xmaou5ktHzTC2Mramxj9AGKjmHl0oDjKmeZlcaZ+hu 0Not9H4xuvXUTlR5S29Wb++O8vhlirpbxmn1SS1ELLFiGThZTt619lhhKGVuUwcv BJ4Otn1kJ3tHj4qtnwxoArlpZiveFHelfjNv9Ih9XCiHmaB9V34qpqMcnatU2l4R oG4/Fugg8rmHBTtsO6x4 =PsPe -----END PGP SIGNATURE----- --M5bOZW4AjlnlH/us--