Discussion on the effect of InnoDB isolation mode on Mysql performance _mysql

In this article I will discuss a related topic –INNODB transaction isolation mode, as well as their relationship to MVCC (multiple versioning concurrency control) and how they affect MySQL performance.

The MySQL manual provides a proper description of the transaction isolation pattern supported by MySQL – I don't repeat it here, but focus on the performance impact.

SERIALIZABLE – This is the strongest isolation mode, which essentially defeats the overhead of multiple versioning for locking all options, and the concurrency you get, in the case of lock management (which is expensive to set locks). This mode is only used in a very special case in MySQL applications.

Repeatable READ – This is the default isolation level, which is usually pretty good, and also good for the convenience of the application. It reads all the data at the first time (assuming the standard non-lock read is used). But there's a high price to pay –INNODB need to maintain the transaction record, from the very beginning to record, its cost is very expensive. More seriously, programs are frequently updated and hot rows– you really don't want InnoDB to handle rows, it has hundreds of versions.

The impact on performance, both read and write can be affected. Traversing multiple rows with a select query is costly, as is the case with update (update), in MySQL 5.6, especially versioning, which appears to cause serious contention issues.

Here's an example: Run Sysbench in a fully in-memory dataset and start transaction, run the whole table, scan, query several times, and keep the transaction open:

Sysbench--num-threads=64--report-interval=10--max-time=0--max-requests=0--rand-type=pareto 80000000--mysql-user=root--mysql-password=--mysql-db=sbinnodb--test=/usr/share/doc/sysbench/tests/db/update_ Index.lua Run

As you can see, the throughput of the write operation has dropped dramatically and continues to fall, when the transaction is open, not only when the query operation is running. In a repeat isolation mode, when you have chosen a transaction, followed by a long transaction, this may be the worst situation I can find. Of course, you'll also see the regression algorithm (regression) in other situations.

If someone wants to test, you can repeat the following collection of queries I used:

Select AVG (Length (c)) from Sbtest1;
Begin;
Select AVG (Length (c)) from Sbtest1;
Select sleep (+);
Commit

Not only is the default isolation level for repeatable Read, but it can also be used for InnoDB logical backups –mydumper or Mysqldump–single-transaction

These results show that the method of this backup has been recovered too long to be used for large data sets, and this method is also affected by performance and cannot be used in frequently written (write) environments.

The Read Committed mode is very similar to the repeatable read pattern, essentially distinguishing which version is not read from scratch in the transaction, instead of reading from the current statement. So using this mode allows InnoDB to maintain a lot of versions, especially if you don't have a long statements to be allowed to run. If you have a long select to run, such as a report query, the performance impact is still serious.

Usually I think it's a good idea to make the read comitted isolation mode the default, and change it to repeatable read if the application or transactions is necessary.

READ Uncommitted – I think this is the most difficult isolation mode (SAD only 2 documents) and only describes its logical point of view. If you use this isolation mode, you will see all the changes that have occurred in the data control, even those that have not yet been submitted transactions. A good use case for this isolation mode is that you can "watch" to a large, dirty read (dirty reads) UPDATE statement that shows which row has been changed and which has not changed.

If the transaction transaction fails at run time, the statement will show changes that have not been committed and may not be submitted, so be careful to use this pattern. There are some use cases that do not require our 100% accurate data, in which case this pattern becomes very convenient.

So, from the performance point of view, how to embody READ uncommitted? Theoretically, InnoDB can clear the row version, which can be created in read UNCOMMITTED mode even after the statement has started executing. In practice, the statement starts as a row version because of a bug or the details of some complex implementations. So, if you run a long select in the READ UNCOMMITTED declaration, you get a lot of row version creation information, like you used Read Committed. No win here.

An important WIN-READ uncommitted isolation mode from the select aspect means that InnoDB does not need to check the old row version-the last line is always right, which can make a noticeable improvement in performance, especially if the undo space is already overflowing on disk, Finding old row versions can result in a lot of Io reading and writing.

Perhaps the above select AVG (k) from Sbtest1 is the best query example I can find, with a similar amount of update effort. If the READ UNCOMMITTED isolation mode is completed in about a minute, I think it is not done in Read Committed isolation mode because the new index entries are inserted faster than the scan.

Finally think about it: using the InnoDB isolation mode correctly enables your application to get the best performance. The benefits you get may be different, and in some cases it may not be the same. Related to the historical version of InnoDB, there seems to be a lot of work to do, I hope in the future of MySQL can be resolved.