Deciding whether or not to make MySQL durable

Today I had intended to talk about my approach and thought process in configuring a fresh MySQL server running 5.6 with the InnoDB storage engine. However, it didn't quite work out that way. I managed to get side-tracked by decision #0 - whether or not durability is important.

Introduction

By default, MySQL is configured to lie to you. That is that when you modify data there are no guarantees that your changes will be able to survive a catastrophic failure, or to put in simple terms: a power loss. The advantage of doing this, is MySQL is able to perform additional buffering and merging and consequently achieve better performance. However, the downside is also very clear: losing data is problematic, so you need to decide what is appropriate for your use-case.

I tend to argue that there are only two options of durability (yes or no), since you do not get to decide which transaction you lose - it could be a $10 order or a $10 million dollar order. Once you have decided you can potentially lose one transaction, in reality you can probably lose a few more.

I also warn strongly that there is no point in making MySQL durable if you can not make durability guarantees with the layers below MySQL. MySQL is not alone in lying by default - consumer hard drives have buffers which are not battery backed, as do virtualization platforms.

Durable

Frequently you will hear the example:

"We don't process credit cards so we don't need to be durable".

I tend to disagree with this, as it is too simplistic to be useful. Order processing and user management are two equally valuable examples which require durability. For example: I don't want to revoke someone's credentials only to find with a power loss my change did not take effect! I also do not like that this example omits the truth that lost DBA/developer productivity can be more expensive than hardware. Many businesses do not want to be in a situation where a failure requires post-failure investigation to handle any discrepancies.

I would also mention that the cost of providing the added IOPS durable workloads create is much lower now that we have the ability to install SSDs on our database servers. If the required performance can be achieved with durable or not durable, losing durability is a sub-optimization, not a trade-off.

So having discussed it, here are the settings to change MySQL to be durable:

  • sync_binlog=1

For slaves:
- sync_relay_log=1
- relay-log-info-repository=TABLE
- master-info-repository=TABLE

or:
- sync_relay_log=1
- sync_relay_log_info=1
- sync_master_info=1

I recommend using the first option for slaves, which will use an InnoDB table internally. This is new to MySQL 5.6.

Non-durable

It's best to describe this by case study. In the case of inPowered, we have multiple database servers, but by enlarge we are non-durable. I argue that this is the right choice for a few reasons:

  • We do not have much in the way of user data
  • Most of the data can be regenerated by re-scraping web articles
  • Most of the data is valuable in aggregate (tracking likes/shares of articles) so losing a portion is not critical.
  • I have no reason to believe that Amazon EC2 servers provide durable writes (see introduction), nor do I have the ability to do a true pull the plug test.

Settings to change to be non-durable:

  • innodb_flush_log_at_trx_commit=2

There are fewer settings to change when deciding to be non durable, since this is the default.

Conclusion

I confessed that I am currently managing a non-durable installation, but you may detect from my tone that I am by and large a durability proponent. I do not necessarily agree with 'lie by default', but nor do I hold it against MySQL - it is a common choice amongst databases vendors. I also think that users are often heavily swayed by naive benchmarks, so it is hard to take a high moral ground otherwise.

Did I miss anything? Please leave a comment.

Converting MyISAM to InnoDB and a lesson on variance

I'm about to start working through converting a large MySQL installation to InnoDB. For the particular use case durability is desired, and with MyISAM a loss of power almost certainly guarantees data loss. Durability is not available as an option.

So the normal question people ask is.. okay, what do I pay for this feature?

I think if you are looking at the workload in question I actually believe InnoDB will perform better overall. Row-level locking, multiversion concurrency control, and the correct selection of hardware (fast-disks with a RAID controller + batter backed write cache) will really help it shine.

But my problem is that InnoDB could be better in 9 situations, and worse in 1. What matters more than anything else is performance regressions. That is...

"Our Customers Feel the Variance, Not the Mean" - a key concept in Six Sigma.

The way I like to explain this, is that if we were to go to a restaurant and order the same meal - or go to Toyota and order the same car, we should receive the same product. I can't get the equivalent to a race car and you get a Trabant. Nor can we allow some queries that were already fast enough get faster, and other queries become intolerably slower.

So what I am actively trying to do is identify situations where MyISAM will be faster than InnoDB. And so far I have on my watch list:

  1. Table Scans - MyISAM data is far more compact, and typically table scans need to examine far less data.
  2. Index Scans - In InnoDB, indexes will contain multiple versions and typically be much larger.
  3. Inserts - MyISAM has a special optimization where it can just insert into the end of a table. I think even with the battery backed write cache this will be hard to beat.
  4. Single-threaded workloads - maybe there's an hourly cron that in InnoDB will take significantly longer. InnoDB really performs better in concurrent workloads.

Facebook and Percona have previously written about the importance of variance reduction as well.

I should also note that durability is not the only reason to use InnoDB. Operational tasks like backups become much easier. But that's the subject for a different post :)