Re: postgresql latency & bgwriter not doing its job

On Wed, Aug 27, 2014 at 10:15 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-08-27 10:10:49 -0300, Claudio Freire wrote:
>> On Wed, Aug 27, 2014 at 6:05 AM, Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> wrote:
>> >> [...] What's your evidence the pacing doesn't work? Afaik it's the fsync
>> >> that causes the problem, not the the writes themselves.
>> >
>> >
>> > Hmmm. My (poor) understanding is that fsync would work fine if everything
>> > was already written beforehand:-) that is it has nothing to do but assess
>> > that all is already written. If there is remaining write work, it starts
>> > doing it "now" with the disastrous effects I'm complaining about.
>> >
>> > When I say "pacing does not work", I mean that things where not written out
>> > to disk by the OS, it does not mean that pg did not ask for it.
>> >
>> > However it does not make much sense for an OS scheduler to wait several
>> > minutes with tens of thousands of pages to write and do nothing about it...
>> > So I'm wondering.
>>
>> Maybe what's needed, is to slightly tweak checkpoint logic to give the
>> kernel some time to flush buffers.
>>
>> Correct me if I'm wrong, but the checkpointer does the sync right
>> after the reads. Of course there will be about 30s-worth of
>> accumulated writes (it's the default amount of time the kernel holds
>> on to dirty buffers).
>>
>> Perhaps it should be delayed a small time, say 30s, to let the kernel
>> do the writing on its own.
>
> The kernel *starts* to write out pages after 30s, it doesn't finish
> doing so. So I don't think that's going to work.
>
> I think a somewhat smarter version of the explicit flushes in the
> hack^Wpatch I posted nearby is going to more likely to be successful.

That path is "dangerous" (as in, may not work as intended) if the
filesystem doesn't properly understand range flushes (ehem, like
ext3).