Audit a MySQL Instance with MySQLTuner

Quite often we need to perform a so-called “MySQL instance audit”. This common DBA procedure should give you a general view of the MySQL environment. You may be interested in a basic understanding of what kind of operation MySQL performs, how much memory does it use, or how well does it look from the performance point of view. There is no easy out-of-the-box way to do such an audit on a MySQL server. You can use SHOW STATUS and check the list of system variables, but this way can hardly be called DBA-friendly.

Fortunately there are several tools to make this process easier. Among most popular are mysqlreport and MySQLTuner. In this post I’d like to give a brief overview of MySQLTuner.

So, what can MySQLTuner do? Quoting the documentation: “MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. Within seconds, it will display statistics about your MySQL installation and the areas where it can be improved.”

It’s not magic — they don’t use any “hidden” or unknown MySQL features to provide the report. What they do is use SHOW STATUS metrics and provide a user-friendly report, interpreting data this or that way. What makes such tools really valuable is the way they interpret that data.

So we get a nicely formatted report divided into several sections. The Storage Engine Statistics are nice — you can easily see how many tables use this or that storage engine. This may be very helpful when planning backup strategy. It’s also cool that you get the total tables size for each storage engine. Note that you’ll need root-level access to calculate MyISAM tables and indexes size.

The Performance Metrics sections gives you a general idea of how well your MySQL server is doing. It’s easy to see that this environment is read-intensive: 95% Reads and only 5% writes. MySQLTuner tries to turn your attention to things that are wrong (or that it thinks are wrong) by putting [!!] before the line. In this example, it complains about high connection usage, disabled query cache, and a lot of joins performed without indexes and small InnoDB buffer pool size.

The last section is Recommendations. Based on its findings, MySQLTuner suggests adjustment of different parameters to achieve better performance. Some of these recommendation are quite fair, but some are not that good. For example, instead of increasing the value of max_connections, one should try to find out why the current value is not satisfactory. Are there a lot of slow queries, network issues, or any other reason for long-running threads allocating max connections? Adding more memory to make your database fit into the InnoDB buffer pool may sound like a good idea, but usually you can’t easily add another 10-20G of RAM.

I found MySQLTuner very useful when you need to see current MySQL setting and parameters. It’s easy to do with just a single command. But it’s not that good when it comes to recommendations. I can’t say that all recommendations are bad, but you need to have a clear understanding of where this or that adjustment will take you. If you blindly follow these recommendations you’ll probably get into trouble. Once, MySQLTuner recommended me to use --skip-innodb on the system with a lot of InnoDB tables. It was not the latest version, so that was probably a bug.

On one hand, MySQLTuner is a good tool for novice MySQL users who want to get some stats about their system; but on the other hand, such users may follow the recommendation provided by this tool without really understanding what lies behind them. So before making any changes to your MySQL configuration, ask yourself if you really understand how these changes will affect server behaviour.

The next step in auditing a MySQL instance is using more advanced tools (like mysqlreport) to get a comprehensive report on system status. But that’s the topic for another post, so stay tuned.

PYTHIAN®, LOVE YOUR DATA®, and ADMINISCOPE® are trademarks and registered trademarks owned by Pythian in North America and certain other countries, and are valuable assets of our company. Other brands, product and company names on this website may be trademarks or registered trademarks of Pythian or of third parties. Use of trademarks without permission is strictly prohibited.