主页 / server / 问题 / 1022863
Accepted
ErikPerik
Asked: 2020-06-26 00:58:31 +0800 CST

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

  • 772

显然我在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)

自动截断

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

Step 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 |
+-------------------------+------------+----------+

大约 3 天前它被设置为非活动状态，此后一直没有改变。

一件奇怪的事情是，根据以下查询，只有 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 截断撤消日志大小，我缺少什么？

我只是假设截断意味着磁盘上的实际文件会缩小。或许还有别的意思？

mysql mysql8

1 个回答

  1. Best Answer

    在与不断增长的撤消表空间斗争了几天之后，我们终于弄明白了，所以我要分享结果：

    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';

    在交易列表中，有一个是 2 天前的。

    如果您启用了性能架构，您可以获得保存它的进程和查询：

    SELECT *
FROM performance_schema.threads
WHERE processlist_id = thread_id;

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

    • 2

相关问题