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.