Racker Hacker

Downloads:
The latest stable MySQLTuner script is available in plaintext (32K) and gzipped (8K) versions. Beta versions are available via subversion, and you can find the instructions in the Trac repository.

Mailing List:
MySQLTuner has a low-traffic mailing list for new version announcements and bug fixes. To get on the list, simply send an e-mail to mysqltuner-announce-request@mysqltuner.com with SUBSCRIBE in the subject line. The e-mails will appear to come from mysqltuner-announce@mysqltuner.com, so please be sure to add it to your e-mail provider’s white list.

Installation and Requirements:
The script is a basic Perl script that has no additional requirements. At this time, MySQLTuner is compatible with Linux/Unix based operating systems with Perl 5.6 or later. MySQL versions 3.23 and later are supported by the script.

To get started, simply download MySQLTuner and make it executable:

# wget http://mysqltuner.com/mysqltuner.pl
# chmod +x mysqltuner.pl
# ./mysqltuner.pl

License:
MySQLTuner is offered completely free, and is distributed under the GPL. If you enjoy using the script, please let me know! If you enjoy it so much that you’d like to make a small donation, you’re welcome to do so:

Background:
MySQLTuner is a high-performance MySQL tuning script written in perl that will provide you with a snapshot of a MySQL server’s health. Based on the statistics gathered, specific recommendations will be provided that will increase a MySQL server’s efficiency and performance. The script gives you automated MySQL tuning that is on the level of what you would receive from a MySQL DBA.

This script has been derived from many of the ideas in Matthew Montgomery’s MySQL tuning primer script.

Features:

• Memory Usage: Calculates MySQL memory usage at max load and makes recommendations for increasing or decreasing the MySQL memory footprint. Per-thread and server-wide buffer data is calculated separately for an accurate snapshot of the server’s configuration.
• Slow Queries: Reviews the amount of slow queries relative to the total queries. Slow query time limits are also analyzed and recommendations are made.
• Connections: Current and historical connection counts are reviewed.
• Key Buffer: Takes configuration data and compares it to the actual indexes found in MyISAM tables. Key cache hit rates are calculated and variable adjustments are suggested.
• Query Cache: Query cache hit rates and usage percentages are used to make recommendations for the query cache configuration variables.
• Sorting & Joins: Per-thread buffers that affect sorts and joins are reviewed along with the statistics from the queries run against the server.
• Temporary Tables: Variable recommendations are made to reduce temporary tables that are written to the disk.
• Table Cache: Compares total tables opened to the currently open tables. Calculates the table cache hit rate in order to make suggestions.
• Open Files: Determines if the server will approach or run into the open file limit set by the operating system or the MySQL server itself.
• Table Locks: Finds table locking that forces queries to wait and makes suggestions for reducing locks that require a wait.
• Thread Cache: Calculates how many times MySQL must create a new thread to respond to a query.
• Aborted Connections: Finds applications that are not closing connections to MySQL properly.
• Read/Write Ratios: Calculates the percentage of read and write operations on your MySQL installation.

Usage:

# ./mysqltuner.pl --help 

   MySQLTuner 0.9.0 - MySQL High Performance Tuning Script
   Bug reports, feature requests, and downloads at http://mysqltuner.com/
   Maintained by Major Hayden (major@mhtx.net)

   Important Usage Guidelines:
      To run the script with the default options, run the script without arguments
      Allow MySQL server to run for at least 24-48 hours before trusting suggestions
      Some routines may require root level privileges (script will provide warnings)

   Performance and Reporting Options
      --skipsize       Don't enumerate tables and their types/sizes
                       (Recommended for servers with many tables)
      --skipversion    Don't check for updates to MySQLTuner

   Output Options:
      --nogood         Remove OK responses
      --nobad          Remove negative/suggestion responses
      --noinfo         Remove informational responses
      --nocolor        Don't print output in color

Here is a sample of the script’s output on a well optimized server:

 >>  MySQLTuner 0.9.0 - Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with ‘–help’ for additional options and output filtering

——– General Statistics ————————————————–
[OK] You have the latest version of MySQLTuner
[OK] Currently running supported MySQL version 5.0.51a-log
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 491M (Tables: 1424)
[--] Data in InnoDB tables: 9M (Tables: 151)

——– Performance Metrics ————————————————-
[--] Up for: 19d 16h 14m 3s (57M q [33.636 qps], 5M conn, TX: 185B, RX: 5B)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 2.7M per thread and 606.0M global
[OK] Maximum possible memory usage: 743.5M (37% of installed RAM)
[OK] Slow queries: 0% (81/57M)
[!!] Highest connection usage: 100%  (51/50)
[OK] Key buffer size / total MyISAM indexes: 256.0M/210.9M
[OK] Key buffer hit rate: 99.3%
[OK] Query cache efficiency: 71.4%
[!!] Query cache prunes per day: 2752
[OK] Sorts requiring temporary tables: 0%
[OK] Temporary tables created on disk: 2%
[OK] Thread cache hit rate: 99%
[!!] Table cache hit rate: 2%
[OK] Open file limit used: 4%
[OK] Table locks acquired immediately: 99%
[OK] InnoDB data size / buffer pool: 9.8M/50.0M

——– Recommendations —————————————————–
General recommendations:
    Reduce or eliminate persistent connections to reduce connection usage
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    max_connections (> 50)
    wait_timeout (< 15)
    interactive_timeout (< 15)
    query_cache_size (> 256M)
    table_cache (> 4096)