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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com