Re: Change default of checkpoint_completion_target

From: Andres Freund <andres(at)anarazel(dot)de>
To: Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Bossart, Nathan" <bossartn(at)amazon(dot)com>, Stephen Frost <sfrost(at)snowman(dot)net>, Peter Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)com>, Michael Paquier <michael(at)paquier(dot)xyz>, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, Bernd Helmle <mailings(at)oopsware(dot)de>, "tsunakawa(dot)takay(at)fujitsu(dot)com" <tsunakawa(dot)takay(at)fujitsu(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Change default of checkpoint_completion_target
Date: 2021-01-15 22:41:16
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


On 2021-01-15 23:05:02 +0100, Tomas Vondra wrote:
> 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.

It's also not available everywhere...

> 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.

IME the kernel flushing at some later time precisely is the problem,
because of the latency spikes that happen when it decides to do so. That
commonly starts to happen well before the fsyncs. The reason that
setting a very small checkpoint_completion_target can help is that it
condenses the period of unrealiable performance into one short time,
rather than spreading it over the whole checkpoint...


Andres Freund

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2021-01-15 23:09:17 Re: poc - possibility to write window function in PL languages
Previous Message Andres Freund 2021-01-15 22:37:56 Re: Key management with tests