Re: [WIP] cache estimates, cache access cost

On 06/19/2011 06:15 PM, Kevin Grittner wrote:
> I think the point is that if, on a fresh system, the first access to
> a table is something which uses a tables scan -- like select count(*)
> -- that all indexed access would then tend to be suppressed for that
> table. After all, for each individual query, selfishly looking at
> its own needs in isolation, it likely *would* be faster to use the
> cached heap data.
>

If those accesses can compete with other activity, such that the data
really does stay in the cache rather than being evicted, then what's
wrong with that? We regularly have people stop by asking for how to pin
particular relations to the cache, to support exactly this sort of scenario.

What I was would expect on any mixed workload is that the table would
slowly get holes shot in it, as individual sections were evicted for
more popular index data. And eventually there'd be little enough left
for it to win over an index scan. But if people keep using the copy of
the table in memory instead, enough so that it never really falls out of
cache, well that's not necessarily even a problem--it could be
considered a solution for some.

The possibility that people can fit their entire table into RAM and it
never leaves there is turning downright probable in some use cases now.
A good example are cloud instances using EC2, where people often
architect their systems such that the data set put onto any one node
fits into RAM. As soon as that's not true you suffer too much from disk
issues, so breaking the databases into RAM sized pieces turns out to be
very good practice. It's possible to tune fairly well for this case
right now--just make the page costs all low. The harder case that I see
a lot is where all the hot data fits into cache, but there's a table or
two of history/archives that don't. And that would be easier to do the
right thing with given this bit of "what's in the cache?" percentages.

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