James Mansion wrote:
> I thought I was clear that it should present some stats to the DBA,
> not that it would try to auto-tune?
You were. But people are bound to make decisions about how to retune
their database based on that information. The situation when doing
manual tuning isn't that much different, it just occurs more slowly, and
with the potential to not react at all if the data is incoherent. That
might be better, but you have to assume that a naive person will just
follow suggestions on how to re-tune based on that the same way an
auto-tune process would.
I don't like this whole approach because it takes something the database
and DBA have no control over (read timing) and makes it a primary input
to the tuning model. Plus, the overhead of collecting this data is big
relative to its potential value.
Anyway, how to collect this data is a separate problem from what should
be done with it in the optimizer. I don't actually care about the
collection part very much; there are a bunch of approaches with various
trade-offs. Deciding how to tell the optimizer about what's cached
already is the more important problem that needs to be solved before any
of this takes you somewhere useful, and focusing on the collection part
doesn't move that forward. Trying to map the real world into the
currently exposed parameter set isn't a solvable problem. We really
need cached_page_cost and random_page_cost, plus a way to model the
cached state per relation that doesn't fall easily into feedback loops.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books