MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. It's a readonly script, reads the current configuration variables and status data, won't write any configuration file.

It will give you an overview of your server's performance and make some basic recommendations about improvements that you can make after it completespresents in a brief format along with some basic performance suggestions.

Here is the steps MySQLTuner does

MySQLTuner steps

  • Header Print
  • Get login information
  • Set up some OS variables
  • Toss variables/status into hashes
  • Get information about the tuning connexion
  • Check current MySQL version
  • Suggest 64-bit upgrade
  • Show enabled storage engines
  • Show informations about databases (option: --dbstat)
  • Show informations about indexes (option: --idxstat)
  • Display some security recommendations
  • Calculate everything we need
  • Print the server stats
  • Print MyISAM stats
  • Print InnoDB stats
  • Print AriaDB stats
  • Print replication info
  • Make recommendations based on stats
  • Close reportfile if needed
  • Dump result if debug is on

WARNING(copied from )

It is extremely important for you to fully understand each change you make to a MySQL database server. If you don't understand portions of the script's output, or if you don't understand the recommendations, you should consult a knowledgeable DBA or system administrator that you trust. Always test your changes on staging environments, and always keep in mind that improvements in one area can negatively affect MySQL in other areas.

How to get it

The simplest and shortest method to get MySQLTuner is:

wget http://mysqltuner.pl/ -O mysqltuner.pl
or
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt

How to run it

perl mysqltuner.pl
or
mysqltuner.pl # when execute bit is set

Specific usage


Usage: Minimal usage locally

perl mysqltuner.pl 

Usage: Minimal usage remotely

perl mysqltuner.pl --host targetDNS_IP --user admin_user --password admin_password

Usage: Enable maximum output information around MySQL/MariaDb without debugging

perl mysqltuner.pl --buffers --dbstat --idxstat

Usage: Write your result in a file with information displayed

perl mysqltuner.pl --outputfile /tmp/result_mysqltuner.txt

Usage: Write your result in a file without outputting information

perl mysqltuner.pl --silent --outputfile /tmp/result_mysqltuner.txt

Usage: Using template model to customize your reporting file based on Text::Template syntax.

perl mysqltuner.pl --silent --reportfile /tmp/result_mysqltuner.txt --template=/tmp/mymodel.tmpl

Usage: Enable debugging information

perl mysqltuner.pl --debug

Example output:

# perl mysqltuner.pl 
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:  >>  MySQLTuner 1.6.0 - Major Hayden <This email address is being protected from spambots. You need JavaScript enabled to view it.>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.45-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 620K (Tables: 2)
[--] Data in InnoDB tables: 36M (Tables: 133)
[--] Data in MEMORY tables: 0B (Tables: 4)
[!!] Total fragmented tables: 133

-------- Security Recommendations  -------------------------------------------
[OK] There is no anonymous account in all database users
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@::1' has no password set.
[!!] There is not basic password file list !

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 11h 25m 42s (2K q [0.008 qps], 120 conn, TX: 9M, RX: 612K)
[--] Reads / Writes: 67% / 33%
[--] Binary logging is disabled
[--] Total buffers: 700.0M global + 16.2M per thread (80 max threads)
[OK] Maximum reached memory usage: 716.2M (72.43% of installed RAM)
[!!] Maximum possible memory usage: 2.0G (202.26% of installed RAM)
[OK] Slow queries: 0% (0/2K)
[OK] Highest usage of available connections: 1% (1/80)
[!!] Aborted connections: 5.83%  (7/120)
[OK] Query cache efficiency: 76.4% (1K cached / 1K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 180 sorts)
[!!] Temporary tables created on disk: 30% (71 on disk / 234 total)
[OK] Thread cache hit rate: 99% (1 created / 120 connections)
[OK] Table cache hit rate: 96% (186 open / 193 opened)
[OK] Open file limit used: 1% (53/3K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.2% (97M used / 536M cache)
[OK] Key buffer size / total MyISAM indexes: 512.0M/185.0K
[OK] Read Key buffer hit rate: 99.2% (1K cached / 12 reads)
[OK] Write Key buffer hit rate: 95.1% (41 cached / 2 writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB  buffer pool / data size: 36.0M/36.7M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 52.97% (1220 used/ 2303 total)
[OK] InnoDB Read buffer efficiency: 99.74% (463398 hits/ 464608 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 137 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Reduce your overall MySQL memory footprint for system stability
    Reduce or eliminate unclosed connections and network issues
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    tmp_table_size (> 72M)
    max_heap_table_size (> 72M)
    innodb_buffer_pool_size (>= 36M) if possible.

Want to know more about MySQLTuner, see http://mysqltuner.com/