Re: Howto to clear PostgreSQL cache and how to preload all tuples of a table into the cache?

On 12/12/2011 08:42 AM, Stefan Keller wrote:
> I'd like to clear the PostgreSQL cache (e.g. for benchmarking purposes).
> And I'd like to preload all tuples of a table (say mytable_one) into the cache.
>
> AFAIK there is no way to force all caches to be cleared in PostgreSQL
> with an SQL command.
> The only way to achieve this, seems to restart PG (server), which is
> neither an option for benchmarking purposes nor for production.
You haven't specified your OS, Pg version, etc. This is important.

Pg relies on the OS's disk cache, which it has no way to clear or control.

On Linux, check out the "drop_caches"

> But:
> 1. Isn't it possible to achieve a kind-of cache clearing (in a
> reliable way) by simply doing a "select * from mytable_two" given
> mytable_two is at least as large as mytable_one (which is the one we
> want to benchmark)?
No.

If mytable_two is significantly larger than the system memory then
maybe, and only maybe, you'll clear the cache. Nothing stops Pg from
setting posix_fadvise(..., ..., ...,
POSIX_FADV_SEQUENTIAL|POSIX_FADV_NOREUSE) to help the OS more efficently
do the seqscan, though. Even if Pg doesn't do that, nothing stops the OS
from figuring out Pg's intent and limiting how much it caches.

>
> 2. I assume that "select * from mytable_one" loads as much of the
> tuples as it can into the cache. Are there better ways for preloading
> the contents of a table?
>

Nope, again because Pg largely relies on the OS cache.

The OS will *probably* cache the contents of mytable_one when you do a
seqscan on it, but it might not, and it might be correct in not doing so.

--
Craig Ringer