Tuning MySql with MySqlTuner to increase efficiency and performance

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.

1. Install MySqlTuner

apt-get install mysqltuner


2. Run MySqlTuner

mysqltuner


Input your MySql administrative login and password

Please enter your MySQL administrative login:
Please enter your MySQL administrative password:


Here are sample results:

-------- General Statistics --------------------------------------------------
[!!] There is a new version of MySQLTuner available
[OK] Currently running supported MySQL version 5.0.51a-24+lenny2-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: 98M (Tables: 81)
[!!] InnoDB is enabled but isn't being used
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 56d 10h 58m 7s (137M q [28.243 qps], 3M conn, TX: 2B, RX: 1B)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 2.6M per thread and 106.0M global
[OK] Maximum possible memory usage: 368.5M (18% of installed RAM)
[OK] Slow queries: 0% (75K/137M)
[!!] Highest connection usage: 100% (101/100)
[OK] Key buffer size / total MyISAM indexes: 64.0M/79.3M
[OK] Key buffer hit rate: 100.0%
[OK] Query cache efficiency: 78.4%
[!!] Query cache prunes per day: 269788
[OK] Sorts requiring temporary tables: 0%
[!!] Temporary tables created on disk: 99%
[OK] Thread cache hit rate: 99%
[!!] Table cache hit rate: 1%
[OK] Open file limit used: 27%
[OK] Table locks acquired immediately: 99%
 
-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Reduce or eliminate persistent connections to reduce connection usage
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
Variables to adjust:
max_connections (> 100)
wait_timeout (< 28800) interactive_timeout (< 28800) query_cache_size (> 16M)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
table_cache (> 200)


3. Adjust your MySql config file (/etc/mysql/my.cnf) according to the recommendations. Don’t increase or decrease the values too much because it may have negative impact on the server. If this is a production server, just make minor changes each time and test again a few hours/days later and adjust the values again if needed. It may take a few days to figure out the best values for your server.

4. Restart MySql after you have made changes to the config file

/etc/init.d/mysql restart