MySQL tunner

MySQL tuner, easy to install useful script from Mayor Hayden, person I don’t know but I love his helpful script and appreciate his work a lot 🙂

Installation

$ sudo apt-get install mysqltuner

Run the script

$ sudo mysqltuner

You will be asked to enter your MySQL root credentials

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

You will get a very interesting output

Example output on my EC2 medium instance, 4GB RAM, 2 CPU units, 1 GB of real data

——– General Statistics ————————————————–

[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.31-0ubuntu0.12.04.2
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-

[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 921M (Tables: 180)
[–] Data in InnoDB tables: 4M (Tables: 9)
[–] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 13

——– Performance Metrics ————————————————-

[–] Up for: 4h 4m 21s (635K q [43.312 qps], 8K conn, TX: 32B, RX: 117M)
[–] Reads / Writes: 96% / 4%
[–] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 597.8M (15% of installed RAM)
[OK] Slow queries: 0% (1K/635K)
[!!] Highest connection usage: 100% (152/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/230.9M
[OK] Key buffer hit rate: 100.0% (200M cached / 23K reads)
[OK] Query cache efficiency: 37.2% (196K cached / 528K selects)
[!!] Query cache prunes per day: 723400
[OK] Sorts requiring temporary tables: 0% (12 temp sorts / 41K sorts)
[!!] Temporary tables created on disk: 45% (2K on disk / 5K total)
[OK] Thread cache hit rate: 86% (1K created / 8K connections)
[OK] Table cache hit rate: 31% (400 open / 1K opened)
[OK] Open file limit used: 55% (569/1K)
[!!] Table locks acquired immediately: 94%
[OK] InnoDB data size / buffer pool: 4.7M/128.0M

——– Recommendations —————————————————–

General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours – recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
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
Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
max_connections (> 151)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)