MySQL configuration is difficult, and a frequent source of mysterious performance problems.

target hardware

These configurations are for a typical small *nix server or VPS with 512MB or 1GB of RAM. The server is expected to act as the application server, httpd host, and DB server all at once, so MySQL needs to share. Connections are expected to occur on the local socket only. I am assuming you will run about 4 to 12 mongrels or fastcgi processes, and no memcached.

The files are also excellent for using on your development machine. Just divide the your RAM in half when choosing (to leave room for your heavyweight desktop apps).

which mysql version

Use MySQL version 5.0. Even 5.1 would be ok. Do not use 4.x.

Realize that the examples included with MySQL itself (my-medium.cnf, my-large.cnf, etc.) pretty much suck. Do not look to them for guidance. They might have made sense in 1998. It is no longer that year.

If you use Debian or Ubuntu, and installed MySQL via APT or deprec, you will get a strange Debian-specific table-checking configuration. Find the /etc/mysql/ folder and empty out the Debian scripts, turning them into no-ops:

sudo bash -c '> /etc/mysql/debian.cnf'
sudo bash -c 'echo \#\!/bin/bash > debian-start'

Now, onward.

choosing a storage engine

Deciding between MyISAM and InnoDB is a confusing and endless issue. Please read this exhilarating Choose Your Own Adventure novel, available at the special price of $0.00:

• Are you confident in your MySQL abilities?
  • If no, use InnoDB.
  • If yes, keep reading.

• Do you plan to keep good database backups?
  • If no, use InnoDB.
  • If yes, keep reading.

• Do you have a typical content-based web app (a forum, for example—many SELECTs, some INSERTs, very few UPDATEs and DELETEs)?
  • If no, use InnoDB.
  • If yes, keep reading.

• Do you plan to verify referential integrity in only the application layer?
  • If no—if you need real foreign keys and transactions—use InnoDB.
  • If yes, keep reading.

• Do you need the maximum possible database speed?
  • If no, use InnoDB.
  • If yes, use MyISAM.

The end.

Note that lots of very large sites such as eBay, LiveJournal, and Gamespot verify integrity purely at the application layer (for Ebay, a source, for the other two, just hearsay, albeit reliable hearsay).

Also note that the InnoDB configurations I provide are more complicated than the MyISAM configurations. This is because InnoDB does more things for you. However, it can be slower than MyISAM for the typical web application.

backing up your existing data

If you have any data in your DB already that you need to keep, dump it with mysqldump, because your new setup won’t be compatible. Example:

mysqldump -u root --complete-insert --all-databases --comments > full_dump.sql

You can later restore it with:

cat full_dump.sql | mysql -u root

Note that if you are moving from InnoDB to MyISAM, you can have max key length errors, because InnoDB stores UTF8 keys in a more compact way. You could avoid this by using Latin1 instead, or perhaps by messing around with your index and key definitions. It’s a pain.

available configurations

Choose one of the files below, based on the storage engine you want and the amount of physical memory in your server:

• InnoDB/1GB
• InnoDB/512MB
• MyISAM/1GB
• MyISAM/512MB

Backup your existing my.cnf (usually in /etc/ or /etc/mysql/), copy the new configuration over it, and restart MySQL. You will have to run mysql_install_db again. Now load your data back in.

The configuration files are copyright 2007 Cloudburst, LLC and are licensed under the Academic Free License 3.0.

topic for debate

Would a typical small server like this be faster if we removed resources from MySQL and the mongrels, but added a memcached instance? What if we couldn’t page-cache?

shouts

Many thanks to Dormando, formerly of LiveJournal, who taught me much of what I know. The rest comes from bitter experience and reading the MySQL 5.0 docs.