mySQL Tools for the Wise

Even the fastest most beautiful and scalable systems built with the newest, shiny, techno-bobbles will buckle under the weight of poorly written SQL code and a bad database design.  This post, while not a comprehensive list, will introduce a few tools that can be installed and used to help administrators and developers with their database planning and trouble shooting.  Each of these tools could be a post of it's own.  Luckily, the source sites listed below have plenty of information for you peruse.  So, use this article as a jumping off point to learn more.

Visual Explain

A quick line from their site says about the tool, "MySQL Visual Explain tries to reverse-engineer EXPLAIN by re-ordering and dividing the input into parent/child relationships."  As an extention to the EXPLAIN tool it takes things a bit further by providing a nice visual tree-style output for easy reading.  This is a command line tool.  Don't be afraid.  


From their site, "mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL 3.22.x, 3.23.x, and 4.x server. It runs on most Unix systems (including Mac OS X) which have Perl, DBI, and Term::ReadKey installed."

Almost anyone who has used top will testify to the usefulness of the tool for quickly determining the problem child on a system that has become sluggish or stopped reponding.  myTop is a handy top-like tool that shows active query processes that are happening in real time on your mySQL box in a configurable auto-updating screen.  Yes, just like top.  This is often one of the quick first looks I'll take when I get the dreaded "it's slow" email.

SlowQueryLog w/ microslow patch

I mention the slow query log here mostly because I want to mentioned the patch that is available.  Most will be familiar with the slow query log but it doesn't really tell you much about queries that take under 1 second to run.  Who cares!  I hear you saying, that's fast.  Well, sure, but 100,000 queries taking under one second can make things slow too.  It would be nice to drill down on those super-transient little beasts.  Using this patch will allow you to do just that by allowing you to time in microseconds instead of those super slow seconds.

mysqlreport - so nice!

mysqlreport is just a great tool.  It's run as a command line tool and essentially parses the output of mysql status commands into a more readable and accessible format that humans can actually interpret.  Best of all, there is a page on their site that defines what every last little number on the report means.  This is one of the best explanations of what's going on in your MySQL box that I've seen in such a compact form.  Everyone should have this tool ready for use.

What does this mean at a business level?

At a business level, this is an easy one, if not an obvious one.  Better written database code run on better designed database schemas will SAVE you money in the long run.  A rock solid setup at this level will help you avoid the dreaded total rewrite.  When the time comes, having paid attention to the DB design and the SQL itself will pay off as you begin to need to partition your data.  So, when it's crunch time and your site is growing at triple digit rates because you've released the marketing hounds and your virtual networking campaign has brought in crazy amounts of traffic you will be able to handle it with some degree of grace.  Encouraging your systems administrators to install these tools and encouraging your developers to pay attention them often, early, and wisely will save time and money.  Since time is money that's a compelling business argument in my opinion.