Re: Warm-up cache may have its virtue

Qingqing Zhou <zhouqq(at)cs(dot)toronto(dot)edu> writes:

> > In other words, the difference between being in Postgres's buffer cache and
> > being in the filesystem cache, while not insignificant, isn't really relevant
> > to the planner since it affects sequential scans and index scans equally.
>
> The bitmap was proposed since I think it is time to use dominated
> shared_buffer size. Thus, if it is not in buffer cache, it is not in OS
> cache either.

Hm. Personally I have a hunch you're right. But there we have no actual
evidence. The first thing that needs to happen is changes to use O_DIRECT for
everything and then benchmarking one of those big TPC tests with the O_DIRECT
build and a large buffer cache versus a normal build with an traditional
buffer cache size.

If it's anywhere close, even with no prefetching then it ought to be clear
that the costs of double buffering are becoming substantial.

As far as predicting cache hits I think the best Postgres could do is track
the average cache hit rate, either overall for the whole system or perhaps
even per table and index.

The first problem I see with that is that most systems have a mix of OLTP and
DSS queries and the two might have different patterns. Perhaps keeping track
of cache hit rates in multiple buckets based on the estimated number of rows?
Maybe exponentially growing buckets of "1-10" "10-100" "100-1k" "1k-10k", ...

--
greg