Re: Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?

Hi,

On 12/23/2015 08:22 PM, Robert Haas wrote:
> On Wed, Dec 23, 2015 at 2:16 PM, Tomas Vondra
> <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>>> Another point (which Jan Wieck made me think of) is that the optimal
>>> behavior here likely depends on whether xlog and data are on the same
>>> disk controller. If they aren't, the FPW spike and background writes
>>> may not interact as much.
>>
>> I'm not sure what exactly you mean by "optimal behavior" here. Surely if you
>> want to minimize interference between WAL and regular I/O, you'll do that.
>>
>> But I don't see what that has to do with the writes generated by the
>> checkpoint? If we do much more writes at the beginning of the checkpoint
>> (due to getting confused by FPW), and OS starts flushing that to disk
>> because we exceed dirty_(background)_bytes, that surely interferes with
>> reads (which is a major issue for queries).
>
> Well, it's true that the checkpointer dirty page writes could
> interfere with reads, but if you've also got lots of FPW-bloated WAL
> records being written to the same disk at the same time, I would think
> that'd be worse. No?

Yes, sure. My point was that in both cases the "optimal behavior" is not
to get confused by the initially higher amount of WAL (due to FPW), and
track the "real" un-skewed checkpoint progress.

Placing both data and WAL on the same device/controller makes the
interference worse, especially when we have a lot of FPW at the
beginning of the checkpoint.

I.e. there's only one "optimal" behavior for both cases.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services