MySQLTuner is a script that will assess MySQL performance and recommend steps to maximize performance. To install and run MySQLTuner, log in to your VPS as the root user and follow these steps (for CentOS):
Download MySQLTuner:
|
1 |
# wget --no-check-certificate https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl |
make it executable
|
1 |
chmod +x mysqltuner.pl |
Run MySQLTuner:
|
1 |
./mysqltuner.pl |
This is the result I got from a recently created vps I have running with a wordpress blog with very few visitors.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
>> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Logged in using credentials from debian maintenance account. [OK] Currently running supported MySQL version 5.5.31-1~dotdeb.0 [OK] Operating on 32-bit architecture with less than 2GB RAM -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED -InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 593K (Tables: 11) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 4 -------- Security Recommendations ------------------------------------------- [!!] User '@localhost' has no password set. -------- Performance Metrics ------------------------------------------------- [--] Up for: 55d 0h 52m 30s (317K q [0.067 qps], 17K conn, TX: 1B, RX: 29M) [--] Reads / Writes: 81% / 19% [--] Total buffers: 16.0M global + 832.0K per thread (20 max threads) [OK] Maximum possible memory usage: 32.2M (50% of installed RAM) [OK] Slow queries: 0% (0/317K) [OK] Highest usage of available connections: 25% (5/20) [OK] Key buffer size / total MyISAM indexes: 8.0M/194.0K [OK] Key buffer hit rate: 100.0% (89K cached / 3 reads) [OK] Query cache efficiency: 88.8% (213K cached / 240K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts) [!!] Temporary tables created on disk: 30% (184 on disk / 607 total) [!!] Thread cache is disabled [OK] Table cache hit rate: 28% (57 open / 198 opened) [OK] Open file limit used: 7% (74/1K) [OK] Table locks acquired immediately: 99% (32K immediate / 32K locks) -------- Recommendations ----------------------------------------------------- General recommendations: 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 Set thread_cache_size to 4 as a starting value Variables to adjust: tmp_table_size (> 16M) max_heap_table_size (> 4M) thread_cache_size (start at 4) |
Look at the results, rows starting with [!!] are issues that you need to look into. At the end, after the checks are done, recommendations for your installations are given. Many of the recommendations are easy to follow and if there is a recommendation to change a variable, it is most likely done in the /etc/my.cnf file.
If you are uncertain about any results you get, feel free to post your questions below in the comments.
2 comments for “MySQLTuner : Suggestions for optimizing your MySQL installation”