Posted on by morgo

Proposal to change MySQL replication defaults

In the MySQL team @ Oracle, we’ve been discussing changing the default values for MySQL replication so that they are safer to use.
Specifically:

Setting Current Value Proposed New Value
sync_binlog 0 1
master-info-repository FILE TABLE
relay-log-info-repository FILE TABLE

I have written about all of these settings before in my post on deciding whether or not to make MySQL durable. The short version is that:

  • By default, if MySQL crashes you could lose data.
  • You don’t get to chose what you lose, it could be a $10 order, or a $10M order.
  • Independent of losing orders, DBA time is expensive. Having to perform post-failure investigation to handle discrepancies has a high cost.
  • The cost of durability is much lower with SSDs. In a comment, Daniël van Eeden also correctly said that the cost is much lower in 5.6 with group commit (thanks Daniël!).
  • I recommend most users should be running with MySQL as fully durable.

Now to explain each of these proposed changes:

Sync_binlog

By setting sync_binlog=1 you are ensuring that any changes written to the binary log will not be lost in the event of a crash. This is a problem because with the current default of sync_binlog=0, a replication master crashing may lose events and require all slaves to be re-imaged with a fresh copy of data to be consistent.
It’s important to note that changing this setting has a downside: syncing the binary log causes a performance impact. As I mentioned above, this was reduced significantly in MySQL 5.6 with group commit, but it will still be evident in some workloads. The most unfortunate may be in single-threaded performance on systems with hard-drives and no RAID controller with a battery backed write cache.

Master-info-repository/relay-log-info-repository

These two options were first introduced in MySQL 5.6 as part of a feature called transactional replication. When both set to TABLE, they store the internal replication coordinates in an InnoDB table, and COMMIT changes as part of the same transaction as the replication events being applied.
What this means in practical terms, is that slaves are in a consistent state when they crash. Replication can just resume processing events from the point of failure, and data does not need to be re-imaged from the master or another slave.

Conclusion

As with previous proposals, we are seeking feedback on how these changes will impact you.

  • Do you currently use sync_binlog and transactional replication?
  • Have you considered these features, but decided not to switch? (This is a case where we’d really love to hear from you).
  • Do you agree that these changes will make MySQL safer, and easier to administer?

Please leave a comment, or get in touch!