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.


 
John Poole is the founder of Primate Labs and lives in Toronto, Ontario with his wife Deborah. You can find John on Twitter or .