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 g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
