Re: Backport of fsync queue compaction

I don't want to take a bunch of time away from the active CF talking 
about this, just wanted to pass along some notes:

-Back branch release just happening a few weeks ago.  Happy to have this 
dropped until the CF is over.

-Attached is a working backport of this to 8.4, with standard git 
comments in the header.  I think this one will backport happily with git 
cherrypick.  Example provided mainly to prove that; not intended to be a 
patch submission.

-It is still possible to get extremely long running sync times with the 
improvement applied.

Since I had a 8.4 server manifesting this problem where I could just try 
this one change, I did that.  Before we had this:

> 2012-06-17 14:48:13 EDT LOG: checkpoint complete: wrote 90 buffers
> (0.1%); 0 transaction log file(s) added, 0 removed, 14 recycled;
> write=26.531 s, sync=4371.513 s, total=4461.058 s

After the compaction code was working (also backported the extra logging 
here) I got this instead:

2012-06-20 23:10:36 EDT LOG:  checkpoint complete: wrote 188 buffers 
(0.1%); 0 transaction log file(s) added, 0 removed, 7 recycled; 
write=31.975 s, sync=3064.270 s, total=3096.263 s; sync files=308, 
longest=482.200 s, average=9.948 s

So the background writer still took a long time due to starvation from 
clients.  But the backend side latency impact wasn't nearly as bad 
though.  The peak load average didn't jump into the hundreds, it only 
got 10 to 20 clients behind on things.

Anyway, larger discussion around this and related OS tuning is a better 
topic for pgsql-performance, will raise this there when I've sorted that 
out a bit more clearly.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com