A new pilot light for heating up Evergreen searches

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:

  1. It encourages virtuous laziness by combining a query specifying the relations to cache with the act of caching them.
  2. It makes it easy for a DBA to warm the cache without requiring shell access to the database server.
  3. If your server has limited RAM, it gives you more control. For example, you could write cronjobs that cache the tables used for hold re-targeting at night, then cache the indexes used for catalog search in the morning after the hold targeter has finished.

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.