Joe Hayes {dot} Org

Sorry, this isn’t one of those new, overpriced yellow books at your local bookstore. It’s not even a book, rather a blog post about a handy tool I came across. It’s a script that’ll help you configure your mysql settings, ideal for those that aren’t fluent in database server optimization. The author doesn’t include instructions, so I’ll help make it easy for those that aren’t familiar with executing shell scripts or using ssh.

First, if you don’t already have an ssh client (much more secure than using telnet), I recommend putty. It’s a free Windows utility. You can download putty.exe from here. Nothing to install, just run it as-is. Besides using it for this tutorial, you can also run useful commands like “service mysql restart”, “service httpd restart”, and “top” to name a few. At any rate, let’s get into the mysql optimization.

You can see / edit your mysql configuration file using pico, so open putty -> type in your web address -> enter your login name (probably “root”) -> enter password and you’re in. Before we go any farther, and assuming your mysql config file is located at etc/my.cnf, let’s make a backup. Type “cp /etc/my.cnf /etc/my.cnf.bak” (without the quotes) which simply makes a copy of that file in case something goes wrong (which can and will when you play with these kinds of files). To get to your mysql config file, type ”pico /etc/my.cnf”.  Here you’ll get a visual view of what’s in the file… yours likely will be a bit different.

Now, from here I’m not even going to begin to suggest what the best settings for your server / VPS might be… not only because I’m not an expert but also because every setup is different. Different memory and processor performance, your programs might be taxing your server differently, etc. But, this is why I wrote this – a great program I found written by Matthew Montgomery, called the MySQL Performance Tuning Primer Script and found here. Don’t bother downloading it to your computer, we’ll do it through ssh. Ps – before using the script make sure you mysql service had been running for at least 48 hours.

First, install it. While logged into your server through ssh, type “wget http://day32.com/MySQL/tuning-primer.sh” to download it, then “chmod u+x tuning-primer.sh” to set the proper permissions, and finally “./tuning-primer.sh” to run the program. The script will analyze your server and suggest the proper settings for settings such as Slow Query Log, Max Connections, Worker Threads, Key Buffer, Query Cache, Sort Buffer, Joins, Temp Tables, Table (Open & Definition) Cache, Table Locking, Table Scans (read_buffer), and Innodb Status.

Once you get your suggestions, exit out of the script back to the command prompt (ctrl X), then pico back into mysql’s config file “pico /etc/my.cnf” and change the settings accordingly. When finished, ctrl X back out and type “y” to save and overwrite the file (remember your backup!) You’ll need to restart mysql for the changes to take effect, so type “service mysql restart”, then I’d run a “top” command to watch the server loads… hopefully after things settle the load will be much better. Pay especial attention to the user “mysql” and how much CPU and memory it’s taking up.

I’d give it a couple days and run this optimizer again, remembering all you have to do now that it’s installed is type “./tuning-primer.sh” to run it. To close putty simple type “exit”. This is much easier then how I normally do it (playing with settings for days, watching changes work and not work – over and over), and likely better probably too. Happy optimizing!