Popular Redux

I mentioned last week that the Geekbench Browser has been rather popular lately; so popular that, for a while, it was having trouble keeping up with the increased traffic. While the server didn’t crash, a lot of the requests were timing out; the requests that didn’t were taking almost a minute to load.

Now, the Geekbench Browser is written in Ruby on Rails and is running on a server with only 512MB of RAM; I was concerned that Rails’ memory requirements were starving the database server, causing simple queries to take a third of a second to execute:

Geekbench2Metric Load (0.304654) SELECT * FROM geekbench2_metrics
WHERE (geekbench2_metrics.geekbench2_document_id = 7275)
AND (geekbench2_metrics.`metric_id` = 4)
LIMIT 1

A third of a second isn’t an outrageous amount of time for a query, but when a page makes fifty queries, each of which take about a third of a second, it really begins to add up.

I copied the Geekbench Browser database over to my development machine (which has a lot more memory) and started running some tests. Lo and behold the database was still taking a third of a second for simple queries like the one above.

Then it hit me. The problem wasn’t with Rails or a lack of memory. The problem was that MySQL was performing a full table scan when executing simple queries like the one above. Full table scans are fast when the table is small (like it was when I was developing and testing the Result Browser) but get slower as the table gets bigger. No wonder I didn’t notice this during development!

The best way to avoid table scans is by adding an index to the table. I whipped up the following migration to add indexes to the geekbench2_metrics table:

class AddIndexes < ActiveRecord::Migration
  def self.up
    add_index :geekbench2_metrics,
        [ :geekbench2_document_id, :metric_id ],
        :unique => true,
        :name => :idx_document_id_metric_id
  end

  def self.down
    remove_index :geekbench2_metrics, :name => :idx_document_id_metric_id
  end
end

and after applying the migration the simple query got a whole lot faster:

Geekbench2Metric Load (0.000473) SELECT * FROM geekbench2_metrics
WHERE (geekbench2_metrics.geekbench2_document_id = 7275)
AND (geekbench2_metrics.`metric_id` = 4)
LIMIT 1

Hooray! I don’t have to worry about upgrading my server hardware any time soon, and my respect for Rails has increased; hey, maybe it’s not so slow after all! Now, if only databases would index themselves I’d be set.