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.