Pierre's Blog

Greeting all,

I had to tune my personal MySQL server recently, and I found a nice tool which helps and advise you for free on the basic way to do it…

This tools is perl based application, I used it on my Master/Slave replication server to select the best parameters values for caching and optimizing my MySQL instance.

This tool is call MySQLTuner,  it’s free and open source perl application that check and display various informations and advice about your MySQL configuration.

Here is a link to get the latest version of it: http://github.com/rackerhacker/MySQLTuner-perl/downloads

After untaring the package, simply run ./mysqltunner.pl

It will probably ask you for administrator login/password for your MySQL server.

Here is the result on our servers:

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.51a-24+lenny3-log

[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

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

[--] Data in MyISAM tables: 102M (Tables: 130)

[!!] InnoDB is enabled but isn't being used

[!!] Total fragmented tables: 17

-------- Security Recommendations  -------------------------------------------

[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------

[--] Up for: 22h 49m 3s (839K q [10.224 qps], 30K conn, TX: 550M, RX: 239M)

[--] Reads / Writes: 48% / 52%

[--] Total buffers: 554.0M global + 12.4M per thread (100 max threads)

[OK] Maximum possible memory usage: 1.7G (14% of installed RAM)

[OK] Slow queries: 0% (0/839K)

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

[OK] Key buffer size / total MyISAM indexes: 384.0M/26.4M

[OK] Key buffer hit rate: 100.0% (15M cached / 7K reads)

[OK] Query cache efficiency: 37.5% (134K cached / 359K selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (36 temp sorts / 92K sorts)

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

[OK] Thread cache hit rate: 95% (1K created / 30K connections)

[OK] Table cache hit rate: 43% (183 open / 417 opened)

[OK] Open file limit used: 29% (334/1K)

[OK] Table locks acquired immediately: 99% (818K immediate / 818K locks)

-------- Recommendations -----------------------------------------------------

General recommendations:

 Add skip-innodb to MySQL configuration to disable InnoDB

 Run OPTIMIZE TABLE to defragment tables for better performance

 MySQL started within last 24 hours - recommendations may be inaccurate

Keep in mind this is just a little script that help you optimize your my.cnf for free, if you look for more advanced tools for customizing and monitoring your app, I really suggest you download MonYOG which is probably the most advanced tool on the market!

Enjoy