The Artima Developer Community
Sponsored Link

Ruby Buzz Forum
MySQL Query Analyzer Rails Plugin

0 replies on 1 page.

Welcome Guest
  Sign In

Go back to the topic listing  Back to Topic List Click to reply to this topic  Reply to this Topic Click to search messages in this forum  Search Forum Click for a threaded view of the topic  Threaded View   
Previous Topic   Next Topic
Flat View: This topic has 0 replies on 1 page
Bob Silva

Posts: 94
Nickname: bobsilva
Registered: Feb, 2006

Bob Silva is a Rails Developer for the UMESD
MySQL Query Analyzer Rails Plugin Posted: Sep 21, 2006 12:55 AM
Reply to this message Reply

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:
Feed Description: A growing collection of screencasts that show you how to use the many facets of the wonderful world of rails.
Latest Ruby Buzz Posts
Latest Ruby Buzz Posts by Bob Silva
Latest Posts From Rails Video Tutorials

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

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

Read the README for more information. Any feedback or improvements welcome. Good luck and don't be immature.

Read: MySQL Query Analyzer Rails Plugin

Topic: Farmer Ted uses Hoe to beat Rake (and RubyGems) Previous Topic   Next Topic Topic: Rails ActionMailer Examples: Responses, Welcome Greetings, Sendmail, Templating and Performance

Sponsored Links


Copyright © 1996-2019 Artima, Inc. All Rights Reserved. - Privacy Policy - Terms of Use