Mysql tuning using mysqltuner.pl perl script

MySQLTuner is a perl script that analyses mysql performance. The script will examine mysql parameters set in the server and it will suggest best possible values for these parameters.

How to run
=======
wget http://mysqltuner.pl/mysqltuner.pl
perl mysqltuner.pl

Sample Output
=========
MySQLTuner output will look like this.
————————————————————————-
>> MySQLTuner 1.2.0 – Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with ‘–help’ for additional options and output filtering

——– General Statistics ————————————————–
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.92-log
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 197M (Tables: 423)
[!!] InnoDB is enabled but isn’t being used
[!!] Total fragmented tables: 1

——– Security Recommendations ——————————————-
[OK] All database users have passwords assigned

——– Performance Metrics ————————————————-
[–] Up for: 22d 17h 37m 38s (6K q [0.003 qps], 207 conn, TX: 6M, RX: 421M)
[–] Reads / Writes: 44% / 56%
[–] Total buffers: 2.2G global + 34.2M per thread (100 max threads)
[!!] Maximum possible memory usage: 5.5G (563% of installed RAM)
[OK] Slow queries: 0% (7/6K)
[OK] Highest usage of available connections: 8% (8/100)
[OK] Key buffer size / total MyISAM indexes: 32.0M/42.9M
[OK] Key buffer hit rate: 100.0% (19M cached / 203 reads)
[OK] Query cache efficiency: 40.9% (342 cached / 836 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 22 sorts)
[OK] Temporary tables created on disk: 1% (3 on disk / 225 total)
[OK] Thread cache hit rate: 96% (8 created / 207 connections)
[OK] Open file limit used: 0% (4/8K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[!!] Connections aborted: 8%

——– Recommendations —————————————————–
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Your applications are not closing MySQL connections properly
————————————————————————-

Notes
——–
1. mysqltuner won’t change your mysql configuration file (/etc/my.cnf) automatically. You need to manually edit /etc/my.cnf after backing up old configuration file.
2. You need to restart mysql after editing my.cnf to take effect the changes
3. mysqltuner.pl is a great script and it usually works well, but there is no guarantee that its recommendations will work well for you.

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: