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.