Liminal Existence

SELECT * FROM Everything, or Why Databases Are Awesome.

I’ve just committed a patch to ActiveRecord that prevents a large number of very, very bad queries from hitting your database. Go update your code, ASAP.

We’ve made some pretty significant progress towards scaling Twitter, and we’re now at the point where the majority of requests that hit our site complete in less than 70 ms (mostly API requests), and the really complicated front-end pages that we display complete in less than 160 ms. There are still a lot of hiccups, so the average is higher than that, but we’re constantly working on getting it down.

One of the consistent problems we’ve been facing is errant queries. We’ve been seeing (off and on) queries like:

SELECT * FROM statuses WHERE user_id = 234223 ORDER BY created_at

If you know anything about relational databases, this is a very bad thing, especially when you have users that have more than 20,000 statuses.

One major downside of having an object-relational mapper is that you don’t always control what goes on behind the scenes. In tracking down this problem, first we investigated all our code, and weren’t able to find the source of these problems. Switching tactics, we isolated some test cases that replicated the problem and brought out the big guns: print. This pretty quickly brought us to an obscure corner of the ActiveRecord source (three cheers for source code!), where it became apparent that Rails was doing these gigantic loads from the database every time we saved even a single field in a related object. There are a bunch of mitigating circumstances that mean that this bug doesn’t get triggered all the time, but it’s still really really bad.

Thankfully, the patch will be committed soon has been committed (32 minutes patch-to-commit!), and no-one will have to deal with, as Coda put it: “Arg stabby stab stab stabbity fuck stab” anymore. The fact that no-one noticed really speaks to how freaking awesome relational databases (in our case, MySQL) are these days.

Perhaps underlying all of this is the simple fact that most of the time, ActiveRecord and Rails in general is pretty solid, and Ruby underneath is a fully sound language with which to build high-volume services. Kevin over at PowerSet has more on the topic - they’ve recently announced that they’ll be doing their front-end development in Ruby (up until now, it’s just been a glue language internally).

Comments