Impact of InnoDB Isolation Mode on MySQL Performance

Impact of InnoDB Isolation Mode on MySQL Performance

I have written two articles over the past few months: one is the dangerous debt related to the history of InnoDB transactions, and the other is the truth about the serious performance problems that MVCC may cause MySQL. In this article, I will discuss a related topic-InnoDB Transaction Isolation Mode, their relationship with MVCC (Multi-version concurrency control), and how they affect MySQL performance.

The MySQL Manual provides an appropriate description of the transaction isolation modes supported by MySQL-here I will not repeat it, but focus on the impact on performance.

SERIALIZABLE-this is the strongest Isolation Mode, essentially defeating the overhead of locking all options under multi-version control under the condition of Lock Management (setting the lock is very expensive, and the concurrency you get. This mode is only used in special cases in MySQL applications.

Repeatable read-this is the default isolation level. Generally, it is quite good, and it is also good for the convenience of applications. It reads all data for the first time (assuming that standard non-lock reads are used ). However, there is a high price-InnoDB needs to maintain transaction records, from the very beginning it will be recorded, it is very expensive. Even more serious, programs are frequently updated and hot rows-you really don't want InnoDB to process rows. It has hundreds of versions.

In terms of performance, both reading and writing can be affected. It is costly to use select queries to traverse multiple rows. For update, MySQL 5.6, especially version control, seems to cause serious contention.

The following is an example: Run sysbench completely in the memory, start transaction, run full table, scan and query several times, and keep transaction on:

Sysbench -- num-threads = 64 -- report-interval = 10 -- max-time = 0 -- max-requests = 0 -- rand-type = package -- oltp-table-size = 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 write operation throughput drops sharply and keeps falling. At this time, transaction is on, not only when the query operation is running. In the replicasable isolation mode, if you have selected another transaction, it is followed by a long transaction, which may be the worst case I can find. Of course, you will also see the regression algorithm (regression) in other cases ).

If someone wants to test it, repeat the query set I used below:

Select avg (length (c) from sbtest1;
Begin;
Select avg (length (c) from sbtest1;
Select sleep (300 );
Commit;

Not only the default isolation level of Repeatable Read, but also InnoDB logical backup-mydumper or mysqldump-single-transaction

These results show that the backup method has been restored for too long and cannot be used in large data sets. This method is also affected by performance and cannot be used in environments with frequent write operations.

 

The read committed mode is similar to the repeatable read mode. The essential difference is that none of the versions are READ from the beginning in the transaction, and instead they are READ from the current statement. Therefore, this mode allows InnoDB to maintain many versions less, especially if you do not have a longStatementsTo allow running. If you have a long select to run, such as REPORT query, the impact on performance is still very serious.

 

I usually think the best practice is to use the READ COMITTED isolation mode as the default, and change the application or transactions to REPEATABLE READ if necessary.

Read uncommitted-I think this is the most hard-to-understand Isolation Mode (there are only two sad documents). It only describes its logical viewpoint. If you use this isolation mode, you will see all the changes in the data control, even those transactions that have not been submitted. A good example of this isolation mode is: you can "watch" The UPDATE statement of a large number of dirty reads (dirty reads) to show which rows have been changed and which have not changed.

If a transaction has an error while running, the statement will show that the transaction has not been committed and may not be committed. Therefore, be careful when using this mode. Although some use cases do not require 100% accurate data, this mode becomes very convenient in this case.

So, from the performance perspective, how to embody read uncommitted? In theory, InnoDB can clear row versions. In read uncommitted mode, InnoDB can be created even after the statement has been executed. In practice, because a bug or some complicated implementation details cannot be achieved, the statement is still a row version at the beginning. Therefore, if you run a long SELECT statement in the read uncommitted statement, you will get a lot of row version creation information, just as you use read committed. No win here.

There is also an important win-read uncommitted Isolation Mode in SELECT, which means InnoDB does not need to check the old row version-the last row is always correct, which will significantly improve the performance, especially when the undo space has exceeded the disk, searching for the old row version will cause a large number of IO reads and writes.

Maybe the preceding select avg (k) from sbtest1; is the best query example that I can find. It can update the workload like this. If the read uncommitted Isolation Mode is completed in about one minute, I think it is not completed in the READ COMMITTED Isolation Mode, because the insert speed of new index entries is faster than the scan speed.

Last thought: correct use of the InnoDB isolation mode can give your application the best performance. The benefits you get may be different. In some cases, there may be no difference. It seems that there is a lot of work to be done in relation to the historical versions of InnoDB. I hope to solve this problem in MySQL in the future.

MySQL InnoDB Storage engine lock mechanism Experiment

Startup, shutdown, and restoration of the InnoDB Storage Engine

MySQL InnoDB independent tablespace Configuration

Architecture of MySQL Server layer and InnoDB Engine Layer

InnoDB deadlock Case Analysis

MySQL Innodb independent tablespace Configuration

MySQL performance implications of InnoDB isolation modes

This article permanently updates the link address: