Re: Spread checkpoint sync

Greg Smith wrote:
> I think a helpful next step here would be to put Robert's fsync 
> compaction patch into here and see if that helps.  There are enough 
> backend syncs showing up in the difficult workloads (scale>=1000, 
> clients >=32) that its impact should be obvious.

Initial tests show everything expected from this change and more.  This 
took me a while to isolate because of issues where the filesystem 
involved degraded over time, giving a heavy bias toward a faster first 
test run, before anything was fragmented.  I just had to do a whole new 
mkfs on the database/xlog disks when switching between test sets in 
order to eliminate that.

At a scale of 500, I see the following average behavior:

Clients TPS backend-fsync
16 557 155
32 587 572
64 628 843
128 621 1442
256 632 2504

On one run through with the fsync compaction patch applied this turned into:

Clients TPS backend-fsync
16 637 0
32 621 0
64 721 0
128 716 0
256 841 0

So not only are all the backend fsyncs gone, there is a very clear TPS 
improvement too.  The change in results at >=64 clients are well above 
the usual noise threshold in these tests. 

The problem where individual fsync calls during checkpoints can take a 
long time is not appreciably better.  But I think this will greatly 
reduce the odds of running into the truly dysfunctional breakdown, where 
checkpoint and backend fsync calls compete with one another, that caused 
the worst-case situation kicking off this whole line of research here.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books