Ruby on Rails ActiveRecord Query Caching

In Rails 2.0, ActiveRecord changed the default for per-request SQL query-caching to on. The cache caches the results of each SQL query made during the processing of a single request to the application; the cache is flushed after the request is completed.

Caching can obviously save trips to the database if queries are repeated multiple times in a request, so it makes sense to have it on by default. I ran into a problem which was traced to this caching behaviour the other day, so, as information on query caching seems to be spread across several blog posts, I thought I’d draw together the bits I found.

This post provides a bit more information on how caching is helpful. One important note from that article is:

	it should be noted that if any inserts/updates/deletes get run, then the whole cache gets flushed.

A second important aspect is the simplicity of the cache:

	The query cache is keyed on the raw SQL statement, so it's very minimalist in its complexity. It doesn't know that your identical query with different order conditions is the same query as before --- and will hit the db again. It only caches identical SQL statements, literally.

You can tell a query is being served from that cache because your application’s log file will contain something like the following, where the first line is a “real” load from the database and the latter two are cache hits:

Though mostly useful, under certain circumstances this caching mechanism isn’t desirable. For example, in one application I was working on recently, an action would add a task to a queue implemented in a database table. The action would then poll the database periodically until the task was marked completed (some other process actually carried out the queued tasks).

We were running into strange problems where the ruby code never noticed the changes in the database, and we were not aware of the default query-caching behaviour.

Caching was, obviously in hindsight, the culprit. The initial state of the task was being cached and so the action was always seeing the item in its uncompleted state when it polled the database. Fortunately the answer is quite simple, but took a little digging to find.

Query caching is implemented in the ActiveRecord::QueryCache module, which provides two handy methods to either enable or disable the query cache temporarily:

  1. temporarily turn on the cache
    Model.cache do ...
  • temporarily bypass the query cache
    Model.uncached do ...
  • Putting the task polling code inside an uncached block solved the problem we were having.

    Finally, if you are really interested in the exact behaviour of the cache, a good place to start is the terribly simple QueryCache module, whose code is here.

    I couldn’t find out how to turn query caching on or off at the application level, if there is such a setting; if anyone knows this setting, please drop me a line so I can update this post and link to your site.

    ← Older
    iSync and w910i - Howto
    → Newer
    Stage Two Complete