Most of us who run a server, use the basic stuff, Apache, PHP, MySQL, to provide a web site, a blog, a forum and so on. I also use MySQL on all of my server, but when it comes to a proper optimization, not just table optimization, I’m just dump. I don’t know MySQL server as I should, to be able to provide for my web site and users the speed, stability and security which they would need.

For this I have found mysqltunner.pl. This can be downloaded from mysqltuner.com and it comes as a small Perl script, which checks your MySQL configuration and based on that will give you different tips and tricks how to improve the MySQL server.

I’ve been using this script for a while now and it did help me improve the MySQL server performance.

Here is a sample:

[root@viperhost /root]# mysqltuner.pl

>>  MySQLTuner 1.0.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
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.1.30-log
[OK] Operating on 32-bit architecture with less than 2GB RAM

——– Storage Engine Statistics ——————————————-
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1M (Tables: 47)
[!!] Total fragmented tables: 2

——– Performance Metrics ————————————————-
[--] Up for: 20m 21s (624 q [0.511 qps], 45 conn, TX: 1M, RX: 68K)
[--] Reads / Writes: 70% / 30%
[--] Total buffers: 96.0M global + 960.0K per thread (300 max threads)
[OK] Maximum possible memory usage: 377.2M (37% of installed RAM)
[OK] Slow queries: 0% (0/624)
[OK] Highest usage of available connections: 2% (6/300)
[OK] Key buffer size / total MyISAM indexes: 32.0M/301.0K
[OK] Key buffer hit rate: 97.7% (1K cached / 44 reads)
[OK] Query cache efficiency: 56.0% (252 cached / 450 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 18 sorts)
[OK] Temporary tables created on disk: 17% (5 on disk / 28 total)
[OK] Thread cache hit rate: 86% (6 created / 45 connections)
[OK] Table cache hit rate: 90% (70 open / 77 opened)
[OK] Open file limit used: 3% (141/4K)
[OK] Table locks acquired immediately: 100% (311 immediate / 311 locks)

——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours – recommendations may be inaccurate

As you can see from the sample, this small script can tell you a lot regarding you MySQL configuration, also what to do with it. How to increase some of the variables in the server configuration to improve performance or stability and so on. Now the most simple way to get this script is like this:

Example:

[root@viperhost /root]# cd /usr/local/bin
[root@viperhost /usr/local/bin]# wget http://mysqltuner.com/mysqltuner.pl
–15:06:03–  http://mysqltuner.com/mysqltuner.pl
Resolving mysqltuner.com… 209.20.89.226
Connecting to mysqltuner.com|209.20.89.226|:80… connected.
HTTP request sent, awaiting response… 200 OK
Length: 38688 (38K) [text/plain]
Saving to: `mysqltuner.pl’

15:06:04 (330 KB/s) – `mysqltuner.pl’ saved [38688/38688]

[root@viperhost /usr/local/bin]# chmod +x mysqltuner.pl

After downloading the script and setting executing rights, you just need to type ./mysqltunner.pl, enter your user, most cases “root” and the password for this user. I think the best idea will be to run the script using “root” user, most of the normal users don’t have rights to check different settings and variables in the MySQL Server.

Try it out, I would like to hear some ideas or issues regarding this script. I ran this already on CentOS 5 on 32bits and 64bits also on Ubuntu Server 8.04 and in all cases it worked perfectly.

Good luck!