Proposal to change Replication and InnoDB Settings in MySQL 5.7

In the MySQL Team, we are currently evaluating potential changes to the default server configuration for MySQL 5.7. For more context, please see my earlier post here.

One of the specific changes we would like to make is with the following three related replication and InnoDB settings:

Setting Old Default New Default
binlog_format STATEMENT ROW
transaction_isolation REPEATABLE-READ READ-COMMITTED
innodb_autoinc_lock_mode 1 ("consecutive" lock mode) 2 ("interleaved" lock mode)

We are considering these changes as a package, for the following reasons:

  • Enabling row based binary logging is the safer option for new applications. It allows all types and combinations of statements to be replicated safely (deterministically), and makes slaves more crash-resilient when using features such as temporary tables.

  • InnoDB requires row-based replication in order to provide the READ-COMMITTED isolation level. In making these two changes together, InnoDB performance is improved:

    • There is no InnoDB gap locking except for foreign-key constraint checking and duplicate-key checking. Also, record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition (link to the manual here).

    • INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record without a gap lock on each row inserted into T. If the transaction isolation level is READ COMMITTED, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: In roll-forward recovery from a backup, every SQL statement must be executed in exactly the same way it was done originally.

      CREATE TABLE ... SELECT ... performs the SELECT with shared next-key locks or as a consistent read, as for INSERT ... SELECT.

      When a SELECT is used in the constructs REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB sets shared next-key locks on rows from table s." (link to the manual here)

    It is important to note that READ-COMMITTED represents a relaxed consistency over REPEATABLE-READ, but is also the default isolation level chosen by SQL Server, PostgreSQL and Oracle.

    For some additional context, Peter Zaitsev recently blogged about the advantages of READ-COMMITTED.

  • Setting innodb_autoinc_lock_mode to 2 also requires row based replication, and improves concurrency since the Auto Increment number no longer needs to be locked to provide a consecutive set of numbers for a bulk insert operation. The MySQL manual describes this new lock mode as "interleaved".

It is important to remember that these represent changes to the default configuration, and reflect what we believe is the best default choice for new applications. We will of course continue to fully support the previous configuration settings should a user prefer these.

With that being said, we are seeking feedback from the community: Do you agree that these represent the best set of defaults for new applications?

Please leave a comment, or get in touch!

Update Jan 23 2015: We have decided to withdraw our proposal to change the default isolation level to READ-COMMITTED for MySQL 5.7. We will reevaluate this decision in the the future.

Published by

morgo

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.

  • http://karwin.blogspot.com Bill Karwin

    I love it. Long overdue.

    It's true that making a semantic change to the default tx isolation level could affect existing applications. But changing the default to READ-COMMITTED will make MySQL a little bit more familiar for Oracle users moving to MySQL.

    MySQL users should learn about both tx isolation levels and use them appropriately. But I frequently encounter situations where the gap locks inhibit concurrent throughput, and I wish that the default were READ-COMMITTED.

    Admittedly, I did have a recent issue with a user who had deadlocks under READ-COMMITTED that would have been prevented by the gap locks of REPEATABLE READ. So there are pros and cons to both tx isolation levels, and it must be decided on a case-by-case basis, and that requires deeper understanding of the effects.

    I wonder if interleaved mode for autoinc would relieve the cases when the autoinc lock conflicts with unique key insert intention locks and results in deadlocks? I haven't tested, but it would be great if those cases were to go away.

  • Jeremy Cole

    I am not so sure about changing the default isolation level. I think most applications that would have a real performance benefit from changing it are likely to be ones where they already do, or at least can, control it themselves. However changing the default will break a lot of existing applications in extremely subtle ways, and those users are in the worst position to figure out *why* it's broken.

  • Peter Zaitsev

    Morgan,

    I think all the changes are good one. I think just sticking to the legacy applications and not doing any changes without that is really slowing down the progress. Couple of years after the change the applications will adjust to the new default while benefits will be long term. We had bigger change with storage engine becoming Innodb and yes it caused some problems but it is almost forgotten by now.

    I do not agree with Jeremy about applications which will get performance benefit already change the setting. In my experience way to many people stick to defaults even when they are quite suffering in term of performance.

    Instead of assuming what people who really need performance from MySQL are smart so they can tune 20 options I think we need to move in other direction and make MySQL out of the box to be as optimal as possible, tuning to the hardware size and application behavior. This is what users will increasingly expect

  • gggeek

    a definite +1

  • Simon J Mudd

    Changing the default replication mode to RBR for 5.7 seems to me to be good. It is clear the complications and bugs that arise from the SBR settings make life complex for the developers. This should simplify their work somewhat, and especially avoid bugs while upgrading from major version X to major version X+1. However, it is not a silver bullet. I have had to change back some RBR replication chains back to SBR as performance suffered otherwise. Minimal RBR helps but is not always a solution. All of this depends on the specific use case.

    I agree with Jeremy Cole. Changing the default isolation level seems to me a "it's faster now" excuse. I checked the notes for Oracle, Sybase and DB2 to see what their default settings are and indeed they are already read-committed, yet reading between the lines these settings are this way "for performance reasons". It is clear to anyone who understands what the database has to do that the current behaviour is heavier than read-committed, and I have blogged about a huge amount of undo space (300GB) related to this which I can not free up on some servers I manage. So REPEATABLE READ has its downsides but it also is I think the best setting to use and provides the least surprises.

    My suggestion would be to improve the documentation and additionally make it clearer under what circumstances you might want to use one value over another. Specifically for the case on a read-only slave it may make sense to use READ-COMMITTED.

    On many read-only slaves I manage I do use READ-COMMITTED and from a performance point of view it does help. I also need to be aware of what this change actually does, so in the cases where this is done it's fine. That said I think that REPEATABLE-READ gives you are more consistent view on the database, especially if you do "real transactions". This is what is set on all masters. Much of MySQL documentation is technical about different "settings" but does not explain the functional reasons why one setup may be better than another. For this specific issue there is plenty of good documentation about, but READ-COMMITTED I think will catch more people out than the current setting. Of course this won't be visible as after the event people will silently groan and change the settings...

    I have not checked but think that the transaction isolation level is passed down in replication to a slave. This needs to be done carefully as we see several bugs related to replication from one major version to another. This specific setting may be handled properly already. I'm not sure.

  • J Galbraith

    The only differences I know of between the current and proposed default isolation levels is that read committed does not prevent read-only Predicate-Many-Preceders or read-only Read Skew. These anomalies are not intuitive to most RDBMS users.

    That being said, Read Committed is the only isolation level that provides the same set of guarantees across MS SQL, MySQL, Oracle, and PostgreSQL, so at least users could count on consistency across the four major RDBMS options.

  • Ebs Queries

    Oracle Apps Online & class room Training by KUNCHAM Online IT Training with realtime synonym 10+ Years of exp facculty.Best Online Oracle Apps Training institute in Hyderabad,India, USA complete Oracle Apps tutorial and Training.

    KUNCHAM Online IT Training one of the leading online training technology companies, specializing in immersive Corporate Training technologies

  • Rick James

    Keep in mind that "rolling upgrades" in replication situations are likely to stumble. Presumably the warning will be in the ChangeLog. (But who reads all the changelogs before upgrading?)

    Does this have any impact on Galera / PXC? ROW is already required, but I have not checked on the other two.

    • http://www.tocker.ca/ Morgan Tocker

      The changes in defaults from 5.5 to 5.6 are the very first warning on the upgrade page: http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html

      For patch releases I think it's reasonable that you shouldn't have to read a change log (and we should promise not to break things). However for a major upgrade, I think this is a reasonable ask.

      For these specific settings, I can't see it affecting slaves. But certainly on others it is possible.

  • peterjohn5566

    Pretty good post. I just came across your site and wanted to
    say that I’ve really enjoyed reading your posts. In any case I’ll be
    subscribing to your feed and I hope you will keep a good work!Cheer!

    Regards

    oracle
    online training