Re: [WIP] cache estimates, cache access cost

Greg Smith wrote:

> I'm not too concerned about the specific case you warned about
> because I don't see how sequential scan vs. index costing will be
> any different on a fresh system than it is now.

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.

I see two ways out of that -- one hard and one easy. One way would
be to somehow look at the impact on the cache of potential plans and
the resulting impact on overall throughput of the queries being run
with various cache contents. That's the hard one, in case anyone
wasn't clear. ;-) The other way would be to run some percentage of
the queries *without* considering current cache contents, so that the
cache can eventually adapt to the demands.

-Kevin

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

Greg Smith <greg(at)2ndQuadrant(dot)com> wrote:
> 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?

The problem is that if somehow the index *does* find its way into
cache, the queries might all run an order of magnitude faster by
using it. The *first* query to bite the bullet and read through the
index wouldn't, of course, since it would have all that random disk
access. 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.

-Kevin

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

Greg Smith <greg(at)2ndquadrant(dot)com> wrote:

> 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.

Well, I certainly didn't mean it to be a reason not to move forward
with development -- I wouldn't have raised the issue had you not
said this upthread:

> I don't see how sequential scan vs. index costing will be any
> different on a fresh system than it is now.

All I was saying is: I do; here's how...

Carry on.

-Kevin