This post originated from an RSS feed registered with Ruby Buzz
by Bob Silva.
Original Post: MySQL Query Analyzer Rails Plugin
Feed Title: Rails Video Tutorials
Feed URL: http://www.railtie.net/xml/rss/feed.xml
Feed Description: A growing collection of screencasts that show you how to use the many facets of the wonderful world of rails.
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 makes use of the EXPLAIN sql statement in MySQL to print out how MySQL formed its execution plan. Basically, for each SELECT query your application runs in the development or testing environments, Rails will also print the query execution plan right after it so you can quickly analyze the queries Rails is making and either add indexes, reorder your joins and remove unneeded or redundant indexes.
To install:
script/plugin install http://svn.nfectio.us/plugins/query_analyzer
Read the README for more information. Any feedback or improvements welcome. Good luck and don't be immature.
Previous Topic
