The Blog: September 2011
Make Your Databases Work Smarter, Not Harder
When optimizing code, you profile it, find out where it’s spending most of it’s time, and figure out how to make it spend less time there. As a sysadmin, I tend to wear a lot of hats, but the one I wear the most by far is the DBA hat–I spend far more time hacking on MySQL optimizations than any particular other thing I do. So, in order to make myself more efficient, if I can find a way to streamline the time I spend with MySQL, I can reap the biggest benefits.
It turns out doing so isn’t so hard. Here are some tricks I’ve learned.
The Art of Refactoring
GameBattles is one of our most popular sites, with an active user base in the millions. While we’ve dealt with sites in that scale before, this particular case has been no walk in the park. While we’re a Rails shop, the fact that the site is written in PHP actually has nothing to due with the difficulties we’ve encountered. The real problems are that the codebase is almost a decade old, has been developed by dozens of developers over that time, has grown organically with a clear roadmap defined only recently, and has almost never been refactored.
So, at various intervals over the last year, I (and others) have been digging into the site and trying to clean up what we can. It seemed that the process of what weve been doing is interesting, but talking about it at any length has been something I’ve hesitated to do, as I don’t like talking about something without quantifiable statistics. But, the fact of the matter is, programming is an art, not a science, and anyone who says different is selling something. And the real trick with refactoring is that it doesn’t meet any immediate business goals: it’s purpose is entirely human, as it entirely exists to make a clean and sane working environment. All of the tangible business gains (security, performance, developer velocity, etc.) are all secondary, and so unless your managers “get it,” it can be a hard thing to argue for.
In our case, it’s no secret that GameBattles’ stability was going downhill, and so we needed to do something. And the only way for us to be able to audit for problems was to make the codebase manageable.