MySQLTuner is a Perl script that analyses your MySQL performance and, then generates recommendations based upon the statistics that it has gathered. You should adjust the variables to increase performance. The way, you can tune your my.cnf file to optimise your MySQL server to make it work more efficiently.

We cannot guarantee this will work for you, but insist that any changes are backed up and adequately tested before rolling our to a production environment.

The MySQLTuner website for further info is here.

Install MySQLTuner

You can download the MySQLTuner directly via the terminal –

wget http://mysqltuner.com/mysqltuner.pl

In order to allow the file to run, we have to make it executable –

chmod +x mysqltuner.pl

Once you have done this, you can then run the script. You may need your mysql root password.

./mysqltuner.pl

Here is an example of the output from one of our test servers –

server22:~# ./mysqltuner.pl

>> MySQLTuner 1.3.0 mod – Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Modified by George Liu (eva2000) at http://vbtechsupport.com/
>> Run with ‘–help’ for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.37-cll
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[–] Status: +Archive -BDB -Federated +InnoDB +ISAM -NDBCluster
[–] Data in MyISAM tables: 301M (Tables: 2074)
[–] Data in HEAP tables: 339K (Tables: 9)
[!!] InnoDB is enabled but isn’t being used
[!!] ISAM is enabled but isn’t being used
[!!] Total fragmented tables: 211

——– Performance Metrics ————————————————-
[–] Up for: 19d 12h 13m 30s (1B q [1K qps], 185K conn, TX: 3B, RX: 377M)
[–] Reads / Writes: 58% / 22%
[–] Total buffers: 2.6M per thread and 58.0M global
[OK] Maximum possible memory usage: 320.5M (5% of installed RAM)
[OK] Slow queries: 0% (17/1B)
[OK] Highest usage of available connections: 32% (32/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/72.3M
[OK] Key buffer hit rate: 99.9%
[OK] Query cache efficiency: 99.9%
[!!] Query cache prunes per day: 54549
[OK] Sorts requiring temporary tables: 0%
[!!] Temporary tables created on disk: 28%
[OK] Thread cache hit rate: 99%
[!!] Table cache hit rate: 0%
[OK] Open file limit used: 12%
[OK] Table locks acquired immediately: 99%
[!!] Connections aborted: 20%

——– Recommendations —————————————————–
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Add skip-isam to MySQL configuration to disable ISAM
Run OPTIMIZE TABLE to defragment tables for better performance
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:
query_cache_size (> 16M)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
table_cache (> 64)

You should then read and apply the output carefully, it shows you what changes you should make in the [mysqld] section of your my.cnf file. Each time you make a change to the my.cnf file (/etc/my.cnf), you must restart the mysql service. Personally, I suggest that even the smallest of tweaks you test then run to measure the impact. To restart MySQL –

service mysql restart