Re: postgresql latency & bgwriter not doing its job

Hello Amit,

> I see there is some merit in your point which is to make bgwriter more
> useful than its current form. I could see 3 top level points to think
> about whether improvement in any of those can improve the current
> situation:

> a. Scanning of buffer pool to find the dirty buffers that can
> be flushed.

Yep, that could be more aggressive, or the aggressiveness could be made
into an adjustible parameter. There is a comment about that in the source.
However I tested setting the round to 1s instead of 120s, and it had no
positive effect on my test.

> b. Deciding on what is criteria to flush a buffer

Indeed.

For instance when the IO load is low, there is no reason not to send out
some buffers, it is a free lunch even if it must be done again later. If
the load is high, this is another matter. So it would mean being able to
decide whether the current IO load is low or not. It could be different on
different disk... Hmmm, not that simple.

> c. Sync of buffers

Yes. That is more or less the effect of my rough approach of calling
CHECKPOINT every 0.2 seconds. Andres Freund just implemented a quick
linux-specific patch which does that within CHECKPOINT pacing, and which
greatly improves the situation, although it could still be a little
better.

> [separate xlog test]

I do not have a setup available for that right now. Not sure this would be
an issue for low loads.

--
Fabien.