Using MySQLTuner – MySQL Optimization Tool

Kedar

16 years ago

MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. Within seconds, it will display statistics about your MySQL installation and the areas where it can be improved.

Downloading MySQLTuner:

wget http://mysqltuner.com/mysqltuner.pl

chmod +x mysqltuner.pl

Using MySQLTuner Script for Lampp systems:

Just replace mysql & mysqladmin commands with respect lampp commands

vi mysqltuner.pl

Press Keys:- <ESC> and <:>

Enter:- 1,$s/\`mysql/\`\/opt\/lampp\/bin\/mysql/g

Running mysqltuner and obtaining the performance analysis:

Shell> ./mysqltuner.pl

Following is the Sample Output from mysqltuner script::

>> MySQLTuner 1.0.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: root

Please enter your MySQL administrative password:

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

[–] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.27-log

[!!] Switch to 64-bit OS – MySQL cannot currently use all of your RAM

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

[–] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster

[–] Data in MyISAM tables: 33G (Tables: 1013)

[–] Data in ARCHIVE tables: 22B (Tables: 1)

[!!] Total fragmented tables: 71

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

[–] Up for: 22d 7h 48m 16s (2B q [1K qps], 557K conn, TX: 1B, RX: 3B)

[–] Reads / Writes: 15% / 85%

[–] Total buffers: 1.2G global + 12.3M per thread (100 max threads)

[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability

[!!] Maximum possible memory usage: 2.4G (59% of installed RAM)

[OK] Slow queries: 0% (7K/2B)

[OK] Highest usage of available connections: 25% (25/100)

[OK] Key buffer size / total MyISAM indexes: 1.0G/4.6G

[OK] Key buffer hit rate: 99.7% (8B cached / 30M reads)

[OK] Query cache efficiency: 49.2% (1M cached / 2M selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (207 temp sorts / 49M sorts)

[OK] Temporary tables created on disk: 9% (46K on disk / 511K total)

[OK] Thread cache hit rate: 99% (269 created / 557K connections)

[!!] Table cache hit rate: 0% (64 open / 1M opened)

[OK] Open file limit used: 10% (112/1K)

[OK] Table locks acquired immediately: 99% (224M immediate / 224M locks)

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

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

table_cache (> 64)