☰

MySQL 8 撤消日志在过度增长后不会截断

tag-icon • tag-icon mysql mysql8
MySQL 8 撤消日志在过度增长后不会截断

显然，我SELECT20 天前启动了一个查询，但从未完成。即使客户端已断开连接，它仍继续运行，并且运行时间太长，以至于其中一个撤消日志的大小已增长到 230 GB。（有问题的数据库有 320 GB 的数据）。我强制终止了查询，但撤消日志不会缩减回正常大小，导致磁盘已满。

为了记录，这是磁盘上的大小：

root@the-db:/var/lib/mysql# du -h undo_00* erik_*
11G undo_001
244G    undo_002
1.5G    erik_temporary_undo_004.ibu
22G erik_undo_003.ibu

有趣的是，所有撤消日志都非常庞大？

我试图理解文档，但无法让它发挥作用：https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html

运行版本：

root@the-db:/var/lib/mysql# mysql --version
mysql  Ver 8.0.18 for Linux on x86_64 (MySQL Community Server - GPL)

自动截断

如果我理解正确的话，要让清除线程截断撤消日志自动地，需要满足三个条件：

步骤1. undo log需要大于@@innodb_max_undo_log_size，即：

mysql> SELECT @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
|                 1073741824 |
+----------------------------+

第 2 步@@innodb_undo_log_truncate需要ON

mysql> select @@innodb_undo_log_truncate;
+----------------------------+
| @@innodb_undo_log_truncate |
+----------------------------+
|                          1 |
+----------------------------+

步骤 3. 总共需要有 2 个活动的撤消日志Automated truncation of undo tablespaces requires a minimum of two active undo tablespaces（不确定这是否意味着 2 个活动的前一个被从旋转中取出以进行截断）。

mysql> SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
+-------------------------+------------+----------+
| NAME                    | SPACE_TYPE | STATE    |
+-------------------------+------------+----------+
| erik_temporary_undo_003 | Undo       | active   |
| erik_temporary_undo_004 | Undo       | active   |
| innodb_undo_001         | Undo       | active   |
| innodb_undo_002         | Undo       | inactive |
+-------------------------+------------+----------+

根据上述内容，我们有 3 个活动撤消日志和 4 个总共撤消日志。

然而，似乎什么也没有发生。

手动截断

还有一个用于截断日志的手动过程，它有另一组先决条件。

对于自动截断，步骤 1 和 2 相同。

步骤 3 的不同之处在于Manual truncation of undo tablespaces requires a minimum of three active undo tablespaces（仍然不确定这是否意味着在其中一个退出轮换之前有 3 个处于活动状态）。为了解决我的不确定性，我创建了 2 个额外的撤消表空间。

CREATE UNDO TABLESPACE erik_temporary_undo_003 ADD DATAFILE 'erik_undo_003.ibu';
CREATE UNDO TABLESPACE erik_temporary_undo_004 ADD DATAFILE 'erik_temporary_undo_004.ibu';

步骤 4 是停用需要截断的表空间：

ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;

如果我理解正确的话，这将触发截断，完成后将STATUS是empty。再次，这是当前状态：

mysql> SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;
+-------------------------+------------+----------+
| NAME                    | SPACE_TYPE | STATE    |
+-------------------------+------------+----------+
| erik_temporary_undo_003 | Undo       | active   |
| erik_temporary_undo_004 | Undo       | active   |
| innodb_undo_001         | Undo       | active   |
| innodb_undo_002         | Undo       | inactive |
+-------------------------+------------+----------+

大约三天前它被设置为非活动状态，从那以后就没有改变过。

奇怪的是，根据以下查询，只有 2 个活动撤消日志，而前一个查询显示 3 个。我遗漏了什么吗？

mysql> SHOW STATUS LIKE 'Innodb_undo_tablespaces%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_undo_tablespaces_total    | 4     |
| Innodb_undo_tablespaces_implicit | 2     |
| Innodb_undo_tablespaces_explicit | 2     |
| Innodb_undo_tablespaces_active   | 2     |
+----------------------------------+-------+

Innodb_undo_tablespaces_active至少不应该是 3 吗？

频率

文档中提到了innodb_purge_rseg_truncate_frequency让清除线程更频繁运行的一种方法。默认值为128，示例显示将其设置为32。这在时间方面实际上意味着什么非常不清楚。它只是提到“每 32 次运行”。

To increase that frequency, decrease the innodb_purge_rseg_truncate_frequency setting. For example, to have the purge thread look for undo tabespaces once every 32 timees[sic] that purge is invoked, set innodb_purge_rseg_truncate_frequency to 32.

为了确保万无一失，我将其设置为 1。

mysql> show variables like "%truncate%";
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 1     |
| innodb_undo_log_truncate             | ON    |
+--------------------------------------+-------+

指标

我最近才了解如何获取 InnoDB 指标：

mysql> set global innodb_monitor_enable = all;

当指标可用时，我们会及时更新。

相关配置

mysql> show variables like "%undo%";
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_encrypt  | OFF        |
| innodb_undo_log_truncate | ON         |
| innodb_undo_tablespaces  | 2          |
+--------------------------+------------+

mysql> show variables like "%truncate%";
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 1     |
| innodb_undo_log_truncate             | ON    |
+--------------------------------------+-------+

我缺少什么来让 MySQL 截断撤消日志大小？

我只是假设截断意味着磁盘上的实际文件将缩小。也许它意味着其他什么？

答案1

在花了几天的时间与不断增长的撤消表空间作斗争之后，我们终于找到了解决方案，因此我将分享结果：

SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;

我们有 2/2 个活动的（默认）撤消表空间。1 个大约为 1GB，另一个为 90GB 并且还在增长。

每文档：

位于选定撤消表空间中的回滚段将变为非活动状态，以便不会将其分配给新事务。允许当前正在使用回滚段的现有事务完成。

这部分是关键。据我了解，所有交易都必须在清理之前完成。

我们查找所有正在运行的交易：

SELECT trx.trx_id,
       trx.trx_started,
       trx.trx_mysql_thread_id
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN INFORMATION_SCHEMA.PROCESSLIST ps ON trx.trx_mysql_thread_id = ps.id
WHERE trx.trx_started < CURRENT_TIMESTAMP - INTERVAL 1 SECOND
  AND ps.user != 'system_user';

在交易清单中，有一笔交易已经是两天前的了。

如果启用了性能模式，则可以获取保存它的进程和查询：

SELECT *
FROM performance_schema.threads
WHERE processlist_id = thread_id;

我们终止了该进程，一切在 20 分钟内恢复。所有存储都被回收了。

可以将上述两个查询组合起来，一步即可获取信息：

SELECT (unix_timestamp(now()) - unix_timestamp(trx.trx_started))/3600 as hours,
    trx.trx_mysql_thread_id,
    pt.processlist_user,
    pt.processlist_host,
    pt.processlist_command,
    ps.info
FROM INFORMATION_SCHEMA.INNODB_TRX trx
INNER JOIN INFORMATION_SCHEMA.PROCESSLIST ps ON (ps.id = trx.trx_mysql_thread_id)
INNER JOIN performance_schema.threads pt ON (pt.processlist_id = trx.trx_mysql_thread_id)
WHERE trx.trx_started < CURRENT_TIMESTAMP - INTERVAL 1 HOUR
    AND ps.user != 'system_user';```

相关内容