A very important factor in getting good performance from your Evergreen system, particularly catalog searches, is making sure that your database server’s filesystem cache is hot. In particular, if the PostgreSQL files that make up the indexes on tables in the
metabib schema are already cached in memory, the database won’t have to go to disk to scan them. This is nice even if you use solid-state drives, as RAM is still faster than SSDs.
Back in 2008, Dan Scott described a way to heat up the cache. If your database is small enough (or your server is big enough) that the whole thing can fit in memory, heating up the cache can be as simple as:
cd /var/lib/postgresql/9.1/main/base/cat * > /dev/null
If you don’t have nearly that much RAM, you can use queries on the
pg_class table to figure out the filenames of the indexes and tables you want to cache.
Time marches on, however, and I’m happy to see that there’s a patch by Robert Haas working its way through the PostgreSQL review process that will introduce a function called
pg_prewarm. This function lets you pull a relation into the OS cache by name.
For example, to load all indexes on the
metabib.keyword_field_entry table into the OS cache, you could run:
SELECT pg_prewarm(schemaname || '.' || indexname,'main','prefetch',NULL, NULL)FROM pg_indexesWHERE schemaname = 'metabib'AND tablename = 'keyword_field_entry';
Some nice things about this include:
With any luck,
pg_prewarm will show up as a contrib in 9.3, but for the adventurous, the current version of the patch can be found attached to this post on psql-hackers. This blog post by Raghavendra includes a nice step-by-step guide for installing it.