Tuning MySQL for performance with Ubuntu 11.04 and MySQLTuner

One of my current projects requires extremely heavy use of very large data sets housed in sever MySQL databases and spread across several servers. This data feeds various web apps, business processes, and fulfills many requests. It is data that needs to be highly available at all times and return queries in the shortest amount of time possible. There are several database tables with upwards of 15 million records and in some instances these need to be joined to other tables, used in calculations, etc.

I need results now, not 48 hours from now. Performance is essential but hard to come by with legacy servers made out of repurposed machines. In order to see where the bottlenecks are and try to make the best of the situation I decided to do some searching. We’re running Ubuntu 11.04 servers for this purpose and I needed something that would give me a run down of what’s causing performance issues on each server.

That’s when I found mysqltuner. This handy little guy is a Perl script that looks at your my.cnf and other MySQL installation data and makes recommendations about how to improve performance based on your past usage.

It’s easy to install in Ubuntu server 11.04:

sudo apt-get install mysqltuner

To run it:

sudo mysqltuner

Then simply enter your administratrive user and password and you’ll get a nice little printout like so:

image[2]

This will give you a good set of recommendations about which settings to tweak. From here you can make tweaks, restart MySQL and run the script again to see where you stand.

Hope this will help someone in the same situation!