Re: Load Distributed Checkpoints test results

On Mon, 18 Jun 2007, Simon Riggs wrote:

> Smoother checkpoints mean smaller resource queues when a burst coincides 
> with a checkpoint, so anybody with throughput-maximised or bursty apps 
> should want longer, smooth checkpoints.

True as long as two conditions hold:

1) Buffers needed to fill allocation requests are still being written fast 
enough.  The buffer allocation code starts burning a lot of CPU+lock 
resources when many clients are all searching the pool looking for a 
buffers and there aren't many clean ones to be found.  The way the current 
checkpoint code starts at the LRU point and writes everything dirty in the 
order new buffers will be allocating in as fast as possible means it's 
doing the optimal procedure to keep this from happening.  It's being 
presumed that making the LRU writer active will mitigate this issue, my 
experience suggests that may not be as effective as hoped--unless it gets 
changed so that it's allowed to decrement usage_count.

To pick one example of a direction I'm a little concerned about related to 
this, Itagaki's sorted writes results look very interesting.  But as his 
test system is such that the actual pgbench TPS numbers are 1/10 of the 
ones I was seeing when I started having ugly buffer allocation issues, I'm 
real sure the particular test he's running isn't sensitive to issues in 
this area at all; there's just not enough buffer cache churn if you're 
only doing a couple of hundred TPS for this to happen.

2) The checkpoint still finishes in time.

The thing you can't forget about when dealing with an overloaded system is 
that there's no such thing as lowering the load of the checkpoint such 
that it doesn't have a bad impact.  Assume new transactions are being 
generated by an upstream source such that the database itself is the 
bottleneck, and you're always filling 100% of I/O capacity.  All I'm 
trying to get everyone to consider is that if you have a large pool of 
dirty buffers to deal with in this situation, it's possible (albeit 
difficult) to get into a situation where if the checkpoint doesn't write 
out the dirty buffers fast enough, the client backends will evacuate them 
instead in a way that makes the whole process less efficient than the 
current behavior.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD