mysql 自动提交性能,事务级别隔离如何影响MySQL自动提交模式下的性能？

秒速五厘米 2023-01-24 03:26 28阅读 0赞

![Image 1][]

I have a VBulletin 4.x forum running on my server. Some forum tables were converted into InnoDB for performance reasons according to this instruction. Forum itself does not use transactions (no START TRANSACTION or BEGIN WORK in source code) at all and InnoDB tables are used just to prevent tables from locking on UPDATE queries. Forum is functioning in autocommit mode of course.

Do I understand correctly, that I can change the default server transaction isolation level into READ UNCOMMITED in this case and get some performance gains this way?

解决方案

TL;DR: If your forum is slow, the TRANSACTION ISOLATION LEVEL is most likely not the cause of it and setting it to anything else than the default will hardly help. Setting innodb\_flush\_log\_on\_trx\_commit = 2 will help, but has durability consequences for crashes.

The long version:

The upshot it that in any case the system must be able to ROLLBACK, so not even READ UNCOMMITTED is changing anything that needs to be done on a write.

For reading transactions, the read is slower when the chain of undo log records leading to the view for the reading transaction is longer, so READ UNCOMMITTED or READ COMMITTED may be very slightly faster than the default REPEATABLE READ. But you have to keep in mind that we are talking memory accesses here and it is the disk accesses that slow you down.

On the matter of AUTOCOMMIT: This will synchronize every single write statement to disk. If you have been using MyISAM before and that was good enough, you may want to configure

\[mysqld\]

innodb\_flush\_log\_on\_trx\_commit = 2

in your my.cnf file and restart the server.

That will make the commit write from the mysqld to the file system buffer cache, but delay flushing the file system buffer cache to disk so that it happens only once a second. You will not lose any data on mysqld crash, but you may lose up to 1s worth of writes on hardware crash. The InnoDB will recover automatically, even after hardware crash, though, and the behavior is still better than it was with MyISAM before, even if it is not full ACID. It will be much faster than AUTOCOMMIT without that setting.

[Image 1]: