Subject: Re: Change default of checkpoint_completion_target
To: Andres Freund <andres@anarazel.de>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: "Bossart, Nathan" <bossartn@amazon.com>,
 Stephen Frost <sfrost@snowman.net>,
 Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
 Michael Paquier <michael@paquier.xyz>,
 Alvaro Herrera <alvherre@alvh.no-ip.org>,
 Laurenz Albe <laurenz.albe@cybertec.at>, Bernd Helmle
 <mailings@oopsware.de>,
 "tsunakawa.takay@fujitsu.com" <tsunakawa.takay@fujitsu.com>,
 "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
References: <20201207175329.GM16415@tamriel.snowman.net>
 <5285eb02-3340-7728-9f62-1a4365eda850@enterprisedb.com>
 <20201207191742.GN16415@tamriel.snowman.net>
 <7CFCC359-5786-40DB-90A8-BDB0F08F2ABC@amazon.com>
 <3258483.1607449295@sss.pgh.pa.us>
 <20210115215156.uuouk2u44gac6pts@alap3.anarazel.de>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <9cbef4ca-807f-e65a-08bf-4f376aecea41@enterprisedb.com>
Date: Fri, 15 Jan 2021 23:05:02 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.6.0
MIME-Version: 1.0
In-Reply-To: <20210115215156.uuouk2u44gac6pts@alap3.anarazel.de>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

On 1/15/21 10:51 PM, Andres Freund wrote:
> Hi,
> 
> On 2020-12-08 12:41:35 -0500, Tom Lane wrote:
>> FWIW, I kind of like the idea of getting rid of it completely.
>> Is there really ever a good reason to set it to something different
>> than that?  If not, well, we have too many GUCs already, and each
>> of them carries nonzero performance, documentation, and maintenance
>> overhead.
> 
> I like the idea of getting rid of it too, but I think we should consider
> evaluating the concrete hard-coded value a bit more careful than just
> going for 0.9 based on some old recommendations in the docs. It not
> being changeable afterwards...
> 
> I think it might be a good idea to immediately change the default to
> 0.9, and concurrently try to evaluate whether it's really the best value
> (vs 0.95, 1 or ...).
> 
> FWIW I have seen a few cases in the past where setting the target to
> something very small helped, but I think that was mostly because we
> didn't yet tell the kernel to flush dirty data more aggressively.
> 

Yeah. The flushing probably makes that mostly unnecessary, but we still
allow disabling that. I'm not really convinced replacing it with a
compile-time #define is a good idea, exactly because it can't be changed
if needed.

As for the exact value, maybe the right solution is to make it dynamic.
The usual approach is to leave "enough time" for the kernel to flush
dirty data, so we could say 60 seconds and calculate the exact target
depending on the checkpoint_timeout.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company