InnoDB的撤销日志在读取未提交级别的简单选择之后开始增长

Question

我有一个带有20KQPS(大量插入/更新/删除)的percona (5.7)服务器。

我的问题是:为什么发出一个简单但长的查询(对任何表)，使用trx隔离=读-未提交使历史列表长度(撤销日志)增长？(查询执行后，立即清除历史记录列表)。

下面是代码示例：

SET TRANSACTION ISOLATION LEVEL READ-UNCOMMITTED;
SELECT tt.id, tt.name, SLEEP(1) AS delay FROM table tt; # 100 rows, takes 100 to execute

据我理解，SELECT查询不需要读取视图，也不关心数据的一致性。它只是读取脏的，可能是未提交的数据。但是，在执行过程中，历史列表长度开始快速增长(由于高QPS，其他事务编写很多)，它使mysql无法清除--但是为什么呢？我的事务处于读-未提交模式，它永远不需要旧版本的行，对吗？(如果我错了，请纠正我)。

我只是不明白mysql的逻辑。需要帮助。

我查过"information_schema.INNODB_TRX“了。它显示了trx隔离=读未提交、trx_read_only=1、trx_autocommit_non_blocking=1。我检查了其他长时间运行的事务，没有.我在4个不同的复制品上重复了这一点，并且掌握了相同的行为。我试着没有在大桌子上“睡觉”--同样的行为。我在一个副本上重复了这个，没有任何连接，只有我的和复制的踏板(所以没有人会影响测试)。

UPD 1(征求意见)：

我做了个测试。创建一个表，插入4行。

CREATE TABLE my_table (
  id INT(11) NOT NULL AUTO_INCREMENT
, text VARCHAR(255) DEFAULT NULL
, PRIMARY KEY (id)
) ENGINE = INNODB;

INSERT INTO my_table(id, text) VALUES (1, 'hi'), (3, 'hi 2'), (5, 'hi 3'), (7, 'hi 5');

启动脏读，会话2将在查询启动后插入行。

# session 1, now 2021-12-22 21:20:00
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SELECT tt.id, tt.text, SLEEP(4) AS delay FROM my_table tt;

在会话1启动后开始在这里插入行

# session 2, now 2021-12-22 21:20:22
    INSERT INTO my_table(text) VALUES('hi 7');
    INSERT INTO my_table(text) VALUES('hi 11'); ....

依此类推，我们可以永远插入行，而会话1中的查询永远不会完成。

Answer 1

根据MysqlPerformanceblog.com的主要原因是Innodb表空间的说法，以下是您可能正在经历这些事情的原因：

运行事务--如果事务修改了很多行，那么它必须在撤销段中使用大量的空间，而没有办法绕过它。特别是在更新或删除很多行的事务时要特别小心。在许多情况下，如果应用程序能够处理这些进程，那么更新/删除每个事务可能会有数千行可能更好。注意，ALTER将不需要过多的撤消空间，即使对于非常大的表也是如此，因为它内部每10000行提交一次。运行很长的事务--如果您正在运行很长的事务(即使是选择)，Innodb将无法清除在此事务启动后所做的更改的记录，在默认的可重复读取隔离模式下。这意味着很长的事务是非常糟糕的，导致大量垃圾被容纳在数据库中。它不限于撤消插槽。当我们谈到长期交易时，时间是一个糟糕的衡量标准。将只读数据库中的事务打开几周并不有害，但是如果数据库具有很高的更新率，假设每秒钟修改一次10K+行，甚至5分钟的事务都可以考虑，因为它足以累积大约300万行更改。清除螺纹落后，这是最危险的原因。数据库更新可能比清除线程在不再需要记录时清除记录的速度更快，这意味着撤消空间只能增长，直到它消耗了所有空闲空间(或ibdata1文件的指定最大大小)。“好”之处在于，在此之前，性能通常会开始严重受损，并引起人们的注意。对于这个问题，您可以做一些事情，首先，如果清除线程跟不上，您可以使用诺姆b_最大值_洗净_迟滞使执行修改的线程慢下来。然而，这并非在所有情况下都有效。如果您正在运行XtraDB，也可以使用诺姆b_使用_洗净_线程来使用专用的清除线程，因为它不需要与主线程的其他活动竞争，所以工作速度要快一些。您还可以通过将此变量设置为更高的值来使用多个清除线程，尽管此功能有点实验性。

我很多年前就提到过这个帖子..。

• Jun 04, 2014：我可以在现有服务器上将撤消日志移出ibdata1 1 5.6中的MySQL 5吗？
• Apr 23, 2013：Innodb ibdata1 1文件如何能以5倍的速度增长？_文件_每_桌子准备好了？

可能的建议

• 确保你杀死了所有的睡眠连接。他们可能仍然持有隐形表锁。在有些情况下，InnoDB会在回滚之后保留表锁，而不知道是谁创建了锁(请参阅我的May 13, 2012 post 无法更新innodb表中的某些行.)。如果存在这样的锁并随后将其移除，则历史长度应消失。
• 也许可以用诺姆b_最大值_洗净_迟滞在副本上做实验，看看历史长度是否会下降。

Answer 2

使用tx_isolation=read未提交的查询的读取视图是否应该阻止mysql清除撤消日志？在我的例子中，当查询开始时，我看到了历史列表的瞬间跳转，查询结束时立即删除。

是的，这是正确的行为。清除旧行版本不能超过给定正在运行的查询所需的行版本。

一个查询包含一个快照，而不管您的tx_isolation是什么。也就是说，查询只看到在查询开始时存在的行。如果在查询执行期间创建了新行，则该查询仍然看不到新行。好像每个查询都有自己的可重复读取隔离，直到查询执行结束为止。

这意味着该查询的数据库视图所需的行版本不能被清除，至少在查询完成之前是这样。使用读-未提交不会改变这一点。

当您使用读提交或读未提交时，可以在查询完成后清除行版本，但事务尚未完成。

当您使用可重复读取时，直到事务完成后才能清除行版本。

据我所知，没有合法的阅读权。它不会降低锁定需求，也不会减少快照需求，它只会导致事务可以查看异常数据的情况(例如，在其他事务中部分执行更改)。

我建议不要使用读-未提交，永远。