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
- Howto: How to Reset the MySQL Root Password
- PHP 5.2 and APC (Alternative PHP Cache) Performance
- 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 optimize performance
- mysql performance windows
- key efficiency mysql
- optimize mysql windows
- windows mysql tuner
- mysql table_cache hit rate
- ubuntu mysql optimization
- MySQLTuner
- windows mysqltuner
- mysql optimization windows
- query cache efficiency
- ubuntu optimize mysql
- mysql key efficiency 100%
- mysql windows optimize
- mysql optimization scripts
- optimizar mysql windows
- mysql script optimize
- ubuntu mysql optimize
- how to optimize mysql
- mysqltunner windows
- optimize mysql linux
- script optimize mysql
- mysqltuner query cache efficiency
- Query cache prunes per day
- optimize mysql speed
- optimizing mysql ubuntu
- linux mysql optimization
- windows mysql performance
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?