Re: postgresql latency & bgwriter not doing its job

On Wed, Aug 27, 2014 at 10:10 AM, Claudio Freire <klaussfreire(at)gmail(dot)com> 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.

Errata: just after the writes :-p