Pythian Blog: Technical Track

Audit a MySQL Instance with MySQLTuner

by Danil Zburivsky on Oct 9, 2008 12:00:00 AM

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. Here is a sample output provided by MySQL report: 
 >> MySQLTuner 0.9.9 - Major Hayden 
  >> Bug reports, feature requests, and downloads at https://mysqltuner.com/
  >> Run with '--help' for additional options and output filtering
 [!!] Successfully authenticated with no password - SECURITY RISK!
 
 -------- General Statistics ------------------------
 [--] Skipped version check for MySQLTuner script
 [OK] Currently running supported MySQL version 5.0.51a-community-log
 [OK] Operating on 64-bit architecture
 
 -------- Storage Engine Statistics ----------------
 [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
 [--] Data in MyISAM tables: 12G (Tables: 131)
 [--] Data in InnoDB tables: 21G (Tables: 55)
 [--] Data in MEMORY tables: 0B (Tables: 1)
 [!!] Total fragmented tables: 7
 
 -------- Performance Metrics ----------------------
 [--] Up for: 32d 23h 49m 38s (2B q [738.166 qps], 362M conn, TX: 519B, RX: 134B)
 [--] Reads / Writes: 95% / 5%
 [--] Total buffers: 2.1G global + 6.3M per thread (500 max threads)
 [OK] Maximum possible memory usage: 5.2G (44% of installed RAM)
 [OK] Slow queries: 0% (1K/2B)
 [!!] Highest connection usage: 100% (501/500)
 [OK] Key buffer size / total MyISAM indexes: 340.0M/13.9G
 [OK] Key buffer hit rate: 99.4% (5B cached / 32M reads)
 [!!] Query cache is disabled
 [OK] Sorts requiring temporary tables: 0% (89 temp sorts / 54M sorts)
 [!!] Joins performed without indexes: 82460
 [OK] Temporary tables created on disk: 1% (231K on disk / 20M total)
 [OK] Thread cache hit rate: 99% (501 created / 362M connections)
 [OK] Table cache hit rate: 73% (667 open / 910 opened)
 [OK] Open file limit used: 3% (458/12K)
 [OK] Table locks acquired immediately: 99% (1B immediate / 1B locks)
 [!!] InnoDB data size / buffer pool: 21.6G/1.8G
 
 -------- Recommendations --------------------------
 General recommendations:
  Run OPTIMIZE TABLE to defragment tables for better performance
  Reduce or eliminate persistent connections to reduce connection usage
  Adjust your join queries to always utilize indexes
 Variables to adjust:
  max_connections (> 500)
  wait_timeout (< 10)
  interactive_timeout (= 8M)
  join_buffer_size (> 124.0K, or always use indexes with joins)
  innodb_buffer_pool_size (>= 21G)
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. Links: https://mysqltuner.com/
Topics: MySQL Security Technical Track Open Source

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.