事务级别隔离如何影响MySQL自动提交模式下的性能? [英] How transaction level isolation affects performace in autocommit mode in MySQL?

问题描述

我的服务器上运行着一个VBulletin 4.x论坛.根据此说明.论坛本身根本不使用事务(源代码中完全不执行START TRANSACTION或BEGIN WORK)，并且InnoDB表仅用于防止表锁定在UPDATE查询上.当然，论坛在自动提交模式下起作用.

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.

我是否正确理解，在这种情况下，我可以将默认服务器事务隔离级别更改为 READ UNCOMMITED ，从而以这种方式获得一些性能提升?

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:如果您的论坛运行缓慢，则TRANSACTION ISOLATION LEVEL可能不是造成该问题的原因，并且将其设置为默认值以外的其他值几乎不会帮助.设置innodb_flush_log_on_trx_commit = 2会有所帮助，但会导致崩溃的持久性后果.

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.

长版:

我在 http中写了什么交易隔离级别://mysqldump.azundris.com/archives/77-Transactions-An-InnoDB-Tutorial.html .在 http://mysqldump.azundris.com/categories/32- InnoDB .

结果是在任何情况下系统都必须能够回滚，因此甚至READ UNCOMMITTED都没有改变写操作需要完成的任何事情.

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.

对于读取事务，当导致查看读取事务的视图的撤消日志记录链较长时，读取速度会变慢，因此READ UNCOMMITTED或READ COMMITTED可能比默认的REPEATABLE READ快得多.但是您必须记住，我们在这里谈论的是内存访问，而磁盘访问会使您的速度变慢.

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.

关于AUTOCOMMIT:这会将每个写入语句同步到磁盘.如果您以前使用过MyISAM，并且已经足够好了，则可以配置

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

在您的my.cnf文件中，然后重新启动服务器.

in your my.cnf file and restart the server.

这将使提交从mysqld写入文件系统缓冲区高速缓存，但是会延迟将文件系统缓冲区高速缓存刷新到磁盘，因此它仅每秒发生一次.在mysqld崩溃时，您不会丢失任何数据，但是在硬件崩溃时，您可能会丢失高达1s的写操作.即使在硬件崩溃之后，InnoDB也会自动恢复，并且即使它不是完整的ACID，其行为也仍然比以前的MyISAM更好.如果没有该设置，它将比AUTOCOMMIT快得多.

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.

这篇关于事务级别隔离如何影响MySQL自动提交模式下的性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！