Reducing MySQL durability for testing

A while ago, I wrote how to make MySQL perform durably in production. Today I want to do the opposite: configure MySQL (with the InnoDB storage engine) for when you really don’t care about your data.

Continuous integration/automated testing is a good example of when you want to do this, since you usually test for functionality and not performance characteristics. It also helps in situations when the data is very easy to rebuild.

Obligatory Disclaimer: These settings are not safe for production environments!

Configuration Settings

• Keep the binary log disabled (i.e. comment out any log-bin lines in configuration). For testing, you don’t need to be able to point-in-time restore, or seed replicas with changes.

• Set sync_frm=0. This makes any DDL (schema changes) unsafe, but removes an fsync that is required to make sure the changes are safely on disk.

• Set innodb-flush-log-at-trx-commit=0. This reduces redo log file pressure as data is committed. I would rate this as one of the most important settings to change. The particular case when it will help the most is when you are running on hardware without a battery backed write cache.

• Set innodb-doublewrite=0. This makes recovery unsafe, as InnoDB will not be able to recover from a partially written page. However, as with all these steps, we are working with the assumption that you will just throw out the data when that happens.

• Set innodb-checksums=0. Page checksums are InnoDB’s mechanism to make sure the underlying hardware isn’t failing and corrupting data. It’s usually only a small hit (although more measurable with SSDs), and is only used on loading/unloading pages from disk.

• Set innodb_support_xa=0. This makes sure changes between the binary logs and synchronized. Since we don’t care about recoverability, we can disable it.

• Set innodb-log-file-size and innodb-buffer-pool-size appropriately large. I have a guide here. You can typically go much larger on the log files when you don’t care about recoverability – 4G or 8G.

• In your session set unique_checks=0 and foreign_key_checks=0. These two options are optional, since they are the only recommendation listed here which change behaviour. When set they remove internal constraint checking, which means that a unique key may not actually be unique.

  You may notice these options from files created by mysqldump.

What is an fsync

Many of these settings improve performance specifically because they reduce the number of fsync operations that MySQL needs to do. An fsync is required to keep data safe so it’s not in an in memory buffer when power is lost.

But in making the buffers safe, fsync operations also reduce the amount of request re-ordering and merging that can be done at the layers below MySQL. This will typically drop performance substantially in all situations except when you have a RAID Controller with a battery backed cache.

A special note on libeatmydata

Stewart Smith also maintains a library to disable all fsync operations called libeatmydata. I do not expect it to deliver large performance gains over the configuration recommended above, but I can think one good situation to use it: when you have a desire to keep MySQL configuration the same in testing as it is in production. libeatmydata provides an external way to change MySQL’s behaviour, since it just requires one small change to how mysqld is started.

Did I miss anything? Please leave a comment.