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’s health. Based on the statistics gathered, specific recommendations will be provided that will increase a MySQL server’s efficiency and performance. The script gives you automated MySQL tuning that is on the level of what you would receive from a MySQL DBA.
I ran this on the server that hosts this blog
# 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)
Related posts:
- Slow Query Log Analyzes Tools
- MySQL Optimization and Performance Tips
- Optimize MySQL for Low Memory Use
- MySQL Performance Monitoring and Optimization Tools
- PHP 5.2 and APC (Alternative PHP Cache) Performance
- Howto: How to Reset the MySQL Root Password
- MDB Tools to export (migrate) from mdb (Microsoft Access format) to MySQL
- MySQL Concatenate: Adding String At The End Of Field Data
- Mysql Data Import
- MySQL: Remove Duplicate Entries
Popular Related Items »
Incoming search terms
- mysql optimization script
- mysqltuner windows
- mysql optimize script
- optimize mysql
- optimize mysql performance
- mysqltuner for windows
- mysql tuner windows
- mysql Table cache hit rate
- optimize mysql script
- MySQL tuner for Windows
- mysql optimizer script
- mysql optimize
- optimizing mysql performance
- key buffer hit rate
- mySQL Windows Performance
- mysql key efficiency
- mysqltuner pl windows
- mysql performance windows
- optimize mysql windows
- mysql optimize performance
- key efficiency mysql
- MySQLTuner
- ubuntu mysql optimization
- mysql table_cache hit rate
- windows mysql tuner
- ubuntu optimize mysql
- query cache efficiency
- optimizar mysql windows
- windows mysqltuner
- mysql optimization windows
- mysql windows optimize
- mysql key efficiency 100%
- optimize mysql linux
- script optimize mysql
- mysql optimization scripts
- ubuntu mysql optimize
- mysqltunner windows
- how to optimize mysql
- optimizar mysql linux
- increase table_cache gradually to avoid file descriptor limits
- mysql hit rate
- mysql script optimize
- mysql optimisation script
- mysqltuner query cache efficiency
- mysql table_cache
Nevinnomyssk said,
June 24, 2008 @ 23:19
Amazing how this small script gets all these info. The tips it provides are extremely useful as well! We just tried it on our website and the average query time decreased by 34%
Brad Baumann said,
September 21, 2009 @ 17:59
Would be great if there was a windows version of this script. Has anyone ported it yet?