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

Le 12 décembre 2011 01:42, Stefan Keller <sfkeller(at)gmail(dot)com> a écrit :
> 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.
>
> 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)?

in postgresql cache, no, because such a query will use a sequential
scan and postgreSQL will protect its cache with a ring: read tuples
are stored in in this short(er than shared_memory) cache; recycled
while reading the table.

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

no, see 1/
So far, there were some ideas on postgresql cache clearing/management
but nothing did it because none have evidences that it is useful (for
performances)

You can use pgfincore: http://pgfoundry.org/projects/pgfincore
to monitor your OS cache, and if you have a system with POSIX_FADVISE
support you can make snpashot, restore, preload ..Etc

For your benchmark, just make a good scenario, else your benchmark
does not bench anything but what you supposed that can happen (wihtout
happening). I don't see why someone would want to clear the postgresql
cache *in production* ! The engine will use its internal mecanism to
decide what to keep and what to remove with (we expect) more
intelligence than us.

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation