MySQL where to start performance tuning

Recently I needed to setup an instance of MySQL on CentOS and I found myself once again wonder where to start tweaking to get initial performance of the server beyond defaults. I'm a firm believer that early optimization can lead to problems but I figured there must be some basic common sense things to do with MySQL that would tune it to the environment where it lives. My initial search turned up this page:

http://library.linode.com/databases/mysql/centos-5

I often start with documentation on the linode.com site since I use there VPS services. Rather then just blindly make the changes recommended for a "low" memory environment I decided to do a bit more reading in the MySQL documentation. I was trying to discover what (if any) difference there was between "key_buffer" (shown on this linode page) and "key_buffer_size" (as outlined in the MySQL documentation) I never did find an adequate explanation although one post I found suggested that key_buffer_size was depreciated. (apparently they both will work) In any event, I didn't dig any further because in digging around I found a script that seems to be quite helpful with my original quest to tune MySQL to my server environment.

You can locate the page here:

http://www.day32.com/MySQL/

There are several scripts on this page that may be useful for future stuff but the script MySQL Performance Tuning Primer Script located at the top of the page is the one I started with. I downloaded it using:

wget http://www.day32.com/MySQL/tuning-primer.sh

I reviewed the script (just stating the obvious and if you don't know why then you shouldn't ever download anything) before marking it as execute and running it. This script ran without any problems on CentOS. It dumps a bunch of recommendations to the console and upon the initial review it the recommendations appear to be solid.

Of course, I didn't stop there. I wanted to run the script on a VPS (linode.com) where I have another instance of MySQL. This server is setup with Ubuntu 10.04 (Lucid) and unfortunately the script did not run immediately. I received a nice error message from the script:

"Error: Command line calculator 'bc' not found!"

The script has a dependency on BC, a free command line calculator. I installed this with:

sudo apt-get install bc

to provide the necessary dependency and this got things going on Ubuntu for me.

Overall the script will be most useful if you have been running your MySQL instance for a while and have some useful data for it to review.

REFERENCES
MySQL Scripts by Matthew Montgomery
Reference to key_buffer_size being deprecated
MySQL documentation