This morning we had some database problems. With help from Engine Yard we were able to identify the slower queries and eliminate them.
Here you can see our database exploding:
And the sweet release when we deployed the fix:
Basically, our `events` table has swelled to over 30m rows taking up 18gb of disk space. We have some solid indexes in place, but saving new events and querying are both becoming problems. As we plan to do more fancy things with the dashboard in the future this needs to be addressed.
Currently the plan is twofold:
- Split `events` table into `events` and `events_archive`. Older events will go into `events_archive`, archiving will happen nightly on a cron. This will dramatically reduce the size of the `events` table.
- Re-evaluate every query that touches `events`, make sure it’s using the best possible index.
We’ll update you as we make progress. Thanks!
Thanks for the updates, most sites wouldn’t have been so open about them ;)
I knew rails didn’t scale
@drnic This does not seem a Rails-only issue, like the post clearly indicates this is database issue so you would also experiencing problems if the app was written in PHP, Java or whatever.
http://developers.facebook.com/scribe/
@thijsw I think that was some genuine sarcasm there ;)
@thijsw, I think @drnic’s tongue was over there in his aussie cheek…
Oh I see it clearly, the find’s were the only queries taking longer so I suspect DrNic of hiding the data in his marsupium. Typical aussie ;-)
@drnic what is this “Rail” that you speak of?
Thanks for posting this; our application suffered a similar spike the other day- not fun.
What we are struggling to learn is this: how can we anticipate these ‘tipping points’ so that they don’t sneak up on us all of the sudden, on a Saturday, when I am driving through the middle of Texas, in the rain?
Thank you for clearing this out, I was having really slow responses from github today. =/
Anyway, these guys from Github certainly didn’t read canrailsscale.com :)
http://dev.mysql.com/tech-resources/articles/performance-partitioning.html my friend has been going on about how cool mysql partitioning is for exactly this problem