Le 12 décembre 2011 01:42, Stefan Keller <sfkeller@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
