This post originated from an RSS feed registered with Java Buzz
by Paul Kilroy.
Original Post: MySQL Query Analyzer Rails Plugin
Feed Title: The Frustrated Programmer
Feed URL: http://liberty.phpwebhosting.com/404.html
Feed Description: The problems I encounter on a daily basis, blogged for posterity. All content will be original, contain poor grammar and a few mispellings.
Anyone thats ever grown an application from a couple thousand hits a day to a couple hundred thousand has run into db optimization issues at some point. The best teacher is experience and Bravenet was a great teacher.
During Bravenets prime years, we had six load-balanced clusters, each with 5 webservers and one MySQL database server. Our userbase was partitioned over these 6 clusters. At peak times, our Queries Per Second on the database servers were over 900 on each. Each page made a minimum of 3-5 database queries (this was before caching became common place) and our traffic load was quite high, so high in fact that a poorly optimized PHP page or a non-indexed query would bring Bravenet down instantly requiring us to fix the script, re-commit and restart all the Apaches to come online. At one point, a nasty bug in our ad-serving code has us down for over 24 hours, it turned out that PHP was loading an array of over 10,000 elements on each request. As soon as we fixed it and committed the changes, Bravenet came back to life.
Premature Optimization
Fine concept for code but practicing it with your database is more akin to Immature Optimization. While it's common place to put indexes on your conditions columns and primary/foreign keys, sometimes (especially in Rails Schema) you just forget. If your writing a small application, you may never see the effects of your error, but as your application grows, it will quickly show itself by slowing down the load time of your pages.
MySQL Query Analyzer
With all that said, I wrote a plugin to make it easier to catch those mistakes and stay on top of your database optimizations. This plugin maka quick way to follow your tests a little more closely in your test.log.
In your test setup, add the following line:
def setup
RAILS_DEFAULT_LOGGER.debug "\n\e[0;31mRUNNING TEST CASE: #{name}\e[m\n"
end
When you run your tests, you will now see something similar to this.
Previous Topic
