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.


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.


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

- 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.


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.


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.

Published by


I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I've also worked at Percona and InPowered.

  • Justin Swanhart

    Did you mean sync_binlog=1? log_bin=1 will just enable binary logs and name them "1.000001", etc, which you may not want.

    • Morgan Tocker

      Duh, of course. Thanks for noticing!

  • Daniël van Eeden

    And the binlog group commit feature also makes it less costly to set sync_binlog to 1. (And don't forget that the value for sync_binlog is not a boolean!)

  • Sean Hull

    Morgan, great post.

    I may be coming at this question from a high level, but I think it's important to consider it from the perspective of the business, as well as technical detail level.

    When I speak with clients there is a conversation about what, and another about how. What speaks to what business problem we're trying to solve. It may be storing user comments, content an editor just published, financial transactions, or a purchase that was completed. The how of course is what this blog is all about. How do we select technology, and then how do we configure that technology to the business needs.

    To my mind the entire stack of a typical web application bears this problem of durability. Here's what I mean...

    Suppose I'm sitting in an internet cafe, and I'm buying something on amazon. I click "submit order" and then the network fails. Maybe there's latency, or simply the local router died. Or maybe on Amazon's end, the webserver is overloaded, or crashes after the query is sent to the database. And finally we reach the database tier, and suppose MySQL itself crashes, or the server on which it sits crashes. If you're on AWS, suppose the EBS network is overloaded or they have a network outage in the availability zone you're in.

    Ultimately there are many many points in that chain that can fail, and leave a transaction in a weird state. I think many web applications take the position that a user can retry their transaction, resubmit their content, or resave their edit. Amazon's shopping cart I'm sure has additional checks & balances built into the application itself to prevent duplicate purchases, while avoiding an incomplete purchase.

    With all this in mind, I usually encourage clients to set innodb_flush_log_at_trx_commit=2

    As the performance benefit is definitely what they want. It is frankly too technical a nuanced question for most to grapple with, but to my mind with the fragility of the stack as a whole, its the setting they probably want.

    • Morgan Tocker

      You'll notice in my non-durable example that I cited that we are on EC2 as one of the reasons why we're not durable. Some database (like NDB Cluster) offer durability by ensuring data is on 2+ instances (in-memory synchronous), but this is not available for InnoDB.

      But I'm not sure I agree with your logic. To use your example about submitting an order on Amazon, what could happen is:

      1) Order is placed
      2) Confirmation email is sent
      3) Power is cut

      When #3 happens fast enough that means #1 never happened and the user is left in a confused state. Non durability is an asynchronous failure, which is different from a failure of a network interruption between browser and web server.

      For the case of network interrupt and the user is not sure of the state of the order - they can check their email.

      For the case that the webserver fails mid transaction - none of the order will go ahead.

      • Sean Hull

        I would argue that the user is *always* in a confused state. I've sat my mom while she surfs a slow website. She will click, but when she sees no reaction, assumes maybe she didn't click, or isn't sure. So she clicks again.

        I feel most applications with people at one end exhibit this. Durability promises are only as solid as the weakest link.

        Now a high frequency trading application, that's a different story!

        Speaking of HFT...

  • Sean Hull

    PS: Vis-a-vis sync settings to make slaves more durable, I found performance was *horrible*, and had to disable it.

    If a slave dies I usually have a couple more at the ready. I can rebuild them from hotbackup as necessary.

  • Pingback: database mysql 5.6初始配置调优 | 极客521()