LinuxHelps.com

A blog for Linux Lovers.

Posted by sibu on June 14, 2009

Optimize MySQL Performance With MySQLTuner

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

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

Result

>> 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
Please enter your MySQL administrative login: secret
Please enter your MySQL administrative password: more secret

——– General Statistics ————————————————–
[OK] You have the latest version of MySQLTuner
[OK] Currently running supported MySQL version 5.0.32-Debian_7etch4-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: 71M (Tables: 548)

——– Performance Metrics ————————————————-
[--] Up for: 109d 22h 8m 1s (69M q [7.283 qps], 1M conn, TX: 3B, RX: 3B)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 2.6M per thread and 58.0M global
[OK] Maximum possible memory usage: 320.5M (62% of installed RAM)
[OK] Slow queries: 0% (39/69M)
[OK] Highest usage of available connections: 63% (63/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/65.0K
[OK] Key buffer hit rate: 100.0%
[OK] Query cache efficiency: 80.6%
[!!] Query cache prunes per day: 30287
[OK] Sorts requiring temporary tables: 0%
[!!] Temporary tables created on disk: 59%
[OK] Thread cache hit rate: 99%
[!!] Table cache hit rate: 0%
[OK] Open file limit used: 12%
[OK] Table locks acquired immediately: 99%

——– Recommendations —————————————————–
General recommendations:

* 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

Variables to adjust:

* query_cache_size (> 16M)
* tmp_table_size (> 32M)
* max_heap_table_size (> 16M)
* table_cache (> 64)

Add A Comment