MySQL Query Analyzer Rails Plugin 17

Posted by Bob Silva Thu, 21 Sep 2006 04:54:00 GMT

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 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.

Comments

Leave a response

  1. Joe Ruby about 3 hours later:
    Hmm, not a bad idea. :) I take it that it logs to dev/test.log? Will it also support PostgreSQL (which I think wouldn't be more than trivial)? That's quite a setup Bravenet had. I've admined sites getting up to 50-100K daily uniques (with probably 10x page views) served by a single server (web, app, db, everything). Aggregate queries were the killer -- once I cached those, the sites (each on their own server) held up pretty well (of course all where'd columns were indexed -- another performance killer if not done).
  2. Ruby Licious about 6 hours later:
    This is a good idea, i like your work. (Im using your acts_as_threaded heavily btw :)..) A couple of months ago I thought of an idea that's slighty rellated to your new plugin: Wouldn't it be possible to even suggest optimization/indexes/keys to add to peoples tables? Or Even have a mode do it automaticly after X hours/days or Y amount of queries to get a good base for analysis. Should be pretty easy to single out some basic good candidations for new indexes from slow WHEREs, ORDER BYs and LEFT JOIN ONs? SQL-newbies would love this? :) It could also work as a reminder/support for ppl who know how this works but still have very complex databases. Just a crazy idea...
  3. Anthony Eden about 10 hours later:
    I'd love to try it but it looks like your Subversion repo isn't responding right now. :-(
  4. Bob Silva about 14 hours later:
    Looks like I'm having somoe firewall issues, I'll get it fixed ASAP.
  5. Bob Silva about 14 hours later:
    All fixed now and should be accessible to all.
  6. JGeiger 4 days later:
    If you're using SQL commands to create your test environment: config.active_record.schema_format = :sql The plugin doesn't allow the test databases to be recreated, as it tries to EXPLAIN the table creation. "explain SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'" Would it be possible to add in some flag that only runs the plugin during testing, or at least avoid the rake tasks to prepare the databases? As a quick fix, i removed the require from the init.rb and moved it into the test_helper.rb and it's working now, with the side effect that it doesn't work in development, which is ok for now.
  7. Curtis 5 days later:
    Yeah I agree with JGeiger. I have the same problem.
  8. Curtis 5 days later:
    Yeah I agree with JGeiger. I have the same problem.
  9. Bob Silva 5 days later:
    I'll modify it so that it doesn't catch SHOW * queries as there is no reason to analyze them. Not sure when I'll get to it though.
  10. Colin Mitchell 5 days later:
    I had the same problem, and modified line 40 of lib/query_analyzer to look like this: if sql[0..5].downcase == "select" and @logger and @logger.level = Logger::INFO Thanks for the great plugin! It's been really useful after just a couple days of use.
  11. Simon Nicholls 6 days later:
    Great idea! Repository revision 12 version (the "darn it" release!) works pretty much as-is with PostgreSQLAdapter too. Just a different adapter name, plus "explain analyze" is usually more helpful on PostgreSQL. Could even be extended to auto-warn of detected no-no queries or fail tests. Thanks Bob
  12. Bob Silva 14 days later:
    Just an update, I've updated the SVN with a newer version to ignore SHOW queries.
  13. Matte about 1 month later:
    Thanks for this useful tools to optimize our web applications! I add many indexes to my tables, especially for tables using polymorphism.
  14. Al Brown about 1 month later:
    Woa! This is great. Thanks. But could make it easier to add to my app? Cutting and pasting that plugin command was so hard on the wrist. It took over a minute to get this working.
  15. Luca Mearelli 2 months later:
    thanks for your plugin (which i found through a comment in the rails way) i find invaluable to optimize the database! I have added postgresql and oracle support to it (you can find it here: http://www.spazidigitali.com/2006/12/01/rails-query-analyzer-plugin-now-also-on-oracle-and-postgresql/ ). it would be nice if you could integrate my additions to your codebase as I don't want to fork it :-)
  16. Barry 3 months later:
    How to you 'turn it off' once installed? I want to enable & disable when necessary. Cheers.
  17. Barry 3 months later:
    its okay - i just commented out require 'query_analyzer' in query_analyzer/init.rb
Comments