Re: Cost limited statements RFC

On 6/8/13 5:20 PM, Kevin Grittner wrote:
> I'll believe that all of that is true, but I think there's another
> reason.  With multiple layers of write cache (PostgreSQL
> shared_buffers, OS cache, controller or SSD cache) I think there's
> a tendency for an "avalanche" effect.  Dirty pages stick to cache
> at each level like snow on the side of a mountain, accumulating
> over time.  When it finally breaks loose at the top, it causes more
> from lower levels to break free as it passes.

I explained this once as being like a tower of leaky buckets where each 
one drips into the one below.  Buckets draining out of the bottom at one 
rate, and new water comes in at another.  You can add water much faster 
than it drains, for a while.  But once one of the buckets fills you've 
got a serious mess.

> I think the "sudden onset" of problems from write
> saturation contributes to the complaints.

It's also important to realize that vacuum itself doesn't even do the 
writes in many cases.  If you have a large shared_buffers value, it 
wanders off making things dirty without any concern for what's going to 
disk.  When the next checkpoint shows up is when pressure increases at 
the top.

The way this discussion has wandered off has nicely confirmed I was 
right to try and avoid going into this area again :(

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