Re: Experimental patch for inter-page delay in VACUUM

Tom Lane wrote:

> Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
>
>> How I can see the background writer operating is that he's keeping the
>> buffers in the order of the LRU chain(s) clean, because those are the
>> buffers that most likely get replaced soon. In my experimental ARC code
>> it would traverse the T1 and T2 queues from LRU to MRU, write out n1 and
>> n2 dirty buffers (n1+n2 configurable), then fsync all files that have
>> been involved in that, nap depending on where he got down the queues (to
>> increase the write rate when running low on clean buffers), and do it
>> all over again.
>
> You probably need one more knob here: how often to issue the fsyncs.
> I'm not convinced "once per outer loop" is a sufficient answer.
> Otherwise this is sounding pretty good.

This is definitely heading into the right direction.

I currently have a crude and ugly hacked system, that does checkpoints
every minute but streches them out over the whole time. It writes out
the dirty buffers in T1+T2 LRU order intermixed, streches out the flush
over the whole checkpoint interval and does sync()+usleep() every 32
blocks (if it has time to do this).

This is clearly the wrong way to implement it, but ...

The same system has ARC and delayed vacuum. With normal, unmodified
checkpoints every 300 seconds, the transaction responsetime for
new_order still peaks at over 30 seconds (5 is already too much) so the
system basically come to a freeze during a checkpoint.

Now with this high-frequent sync()ing and checkpointing by the minute,
the entire system load levels out really nice. Basically it's constantly
checkpointing. So maybe the thing we're looking for is to make the
checkpoint process the background buffer writer process and let it
checkpoint 'round the clock. Of course, with a bit more selectivity on
what to fsync and not doing system wide sync() every 10-500 milliseconds :-)

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #