Re: [WIP] cache estimates, cache access cost

Kevin Grittner wrote:
> But its not hard to imagine an application mix where this
> feature could cause a surprising ten-fold performance drop after
> someone does a table scan which could persist indefinitely. I'm not
> risking that in production without a clear mechanism to
> automatically recover from that sort of cache skew

The idea that any of this will run automatically is a dream at this
point, so saying you want to automatically recover from problems with
the mechanism that doesn't even exist yet is a bit premature. Some of
the implementation ideas here might eventually lead to where real-time
cache information is used, and that is where the really scary feedback
loops you are right to be worried about come into play. The idea for
now is that you'll run this new type of ANALYZE CACHE operation
manually, supervised and at a time where recent activity reflects the
sort of workload you want to optimize for. And then you should review
its results to make sure the conclusions it drew about your cache
population aren't really strange.

To help with that, I was thinking of writing a sanity check tool that
showed how the cached percentages this discovers compare against the
historical block hit percentages for the relation. An example of how
values changed from what they were already set to after a second ANALYZE
CACHE is probably useful too.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us