Re: patch for new feature: Buffer Cache Hibernation

On Wed, Jun 1, 2011 at 8:58 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> In the latter case, wouldn't we just trigger the same inefficient
> scattered read of the data that normal database operation would
> trigger, taking about the same amount of time to reach cache-warmth?

If you have a system where you're bandwidth-constrained and processing
queries as fast as you can then yes.

But if you have an OLTP system where queries come in at a fixed rate
and it's latency that matters then there's a big difference. It might
take you hours to prime the cache at the rate that queries come in
organically and for that whole time every query requires multiple
cache misses and multiple seeks and random access reads. Once it's all
primed your whole database might actually fit in RAM and require no
i/o to serve requests. And it's possible that your system is
architected on the assumption that that's the case and performance is
inadequate until the whole database is read in.

Actually in that extreme case you can probably get away with a few dd
commands or perhaps an sql select count(*) on startup. I'm not sure in
practice how wide the use case is in the gap between that extreme case
and more average cases where the difference isn't so catastrophic.

I'm sure there will be people who will say it's big but I would like
to see numbers. And I'm not just talking about the usual knee-jerk
"lets' see the benchmarks" response. I would love to see metrics on a
live database showing users how much of their response time depends on
the cache and how that performance varies as the cache gets warmer.
Right now I think users are kind of in the dark on cache effectiveness
and latency numbers.

--
greg