Tune MySQL using MySQLTuner

Tudip

24 June 2016

Every once in a while you see your MySQL showing increased memory footprint.
You want to optimize MySQL but you do not know where to start from as first you need to know what all you should be tuning.
This where MySQLTuner script can help you. As it’s README says:
“_MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief format along with some basic performance suggestions._”
Let’s first download this script. You can either download it from Github

https://github.com/rackerhacker/MySQLTuner-perl

Or, if you have git installed, you can directly do a git clone:

git clone git://github.com/rackerhacker/MySQLTuner-perl.git

Now let’s make this pl file executable.

cd MySQLTuner-perl/
chmod +x mysqltuner.pl

Now let’s execute this script and find out what is going on with your MySQL:

./mysqltuner.pl

This would give you an audit of your MySQL installation. A sample audit report might look like:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.62-0ubuntu0.10.04.1-log
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 867K (Tables: 312)
[--] Data in InnoDB tables: 55M (Tables: 428)
[!!] Total fragmented tables: 428

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 202d 1h 55m 18s (2M q [0.128 qps], 35K conn, TX: 3B, RX: 486M)
[--] Reads / Writes: 87% / 13%
[--] Total buffers: 58.0M global + 2.7M per thread (151 max threads)
[!!] Maximum possible memory usage: 463.8M (93% of installed RAM)
[OK] Slow queries: 0% (11/2M)
[OK] Highest usage of available connections: 17% (26/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/1.7M
[OK] Key buffer hit rate: 99.4% (1M cached / 7K reads)
[OK] Query cache efficiency: 84.7% (1M cached / 2M selects)
[!!] Query cache prunes per day: 173
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 52K sorts)
[!!] Temporary tables created on disk: 40% (101K on disk / 249K total)
[OK] Thread cache hit rate: 99% (52 created / 35K connections)
[!!] Table cache hit rate: 0% (64 open / 20K opened)
[OK] Open file limit used: 0% (2/1K)
[OK] Table locks acquired immediately: 99% (631K immediate / 631K locks)
[!!] Connections aborted: 59%
[!!] InnoDB data size / buffer pool: 55.4M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Your applications are not closing MySQL connections properly
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 16M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_cache (> 64)
    innodb_buffer_p ool_size (>= 55M)

Follow the recommendation sections and make changes as suggested.
After each config changes, make sure you restart and run the tuner script again to see that the changes are indeed improving your installation.
Also It is most like to recommend that you run OPTIMIZE TABLE.
You can optimize all the databases at one go using following command.

mysqlcheck -op -u root --all-databases=true

That’s it, you should have tuned your MySQL performance. Please drop us a line if you need any help.