top-secret tuned mysql configurations for rails

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:

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.

11 responses

  1. Oh, I’ll just complain about how SQL is obsolete and not do anything about it!

    Also, I think Rails migrations by default create InnoDB tables…

  2. You’re right. But if your my.cnf disables InnoDB, like the MyISAM configs here, the tables get added as MyISAM instead with no troubles.

  3. Assuming the binlog disable is for performance purposes? How much of a slowdown does that cause—and is there an alternate means to getting the transaction-by-transaction restore benefits of the binlog?

    Just recently we got a “I did something really stupid” call from a client (who had dropped their database)—restoring their DB from the binlog right up until that one transaction worked quite nicely.

  4. MySQL 5.0 docs say: “Running the server with the binary log enabled makes performance about 1% slower.”

    I don’t have a number, but my personal experience is that the hit from the binlog is quite a bit more than 1%, especially if it has to write to the same physical volume that your tables and InnoDB logfiles are on. Maybe I’m wrong.

    Feel like running a benchmark (sans ActiveRecord), and making a report?

    I don’t think one should rely on the binlog as a backup… rather, keep real periodic backups. I guess it’s a convenient protection against drunken users, though.

  5. The Debian scripts silently run mysqlcheck and mysql_upgrade at every boot. This is a bit dangerous—if your tables are crashing frequently, you have a serious problem to be resolved, not glossed over. And how often do you upgrade MySQL itself? If you do, do you really want the table upgrade to be performed without your consent?

    That’s all, really—the scripts treat only the symptoms of what could be a deep configuration problem.

  6. Another thing to note is that changing the size of your InnoDB log files without removing the old ones can cause problems. Copy any old InnoDB logs to a separate location before restarting MySQL with the new config (copy instead of delete because you may need to redo this step). See here.

  7. Good advice. I forget if mysql_install_db blows out the existing InnoDB logs or not. I manually delete everything in /var/lib/mysql/, run mysql_install_db, and reload from a full dump if I change log sizes.

Follow

Get every new post delivered to your Inbox.

Join 427 other followers