MySQL 8撤消日志在过度增长后不截断
显然，我20天前开始了一个SELECT查询，但一直没有完成。即使客户端断开了连接，它仍然继续运行，并且运行了很长时间，以至于其中一个撤销日志的大小增加到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.撤消日志需要大于@@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个总撤消日志。
However，似乎什么都没发生。
手动截断
还有一个手动过程来截断日志，它有另一组先决条件。
步骤1和步骤2对于自动截断是相同的。
步骤3在这个Manual truncation of undo tablespaces requires a minimum of three active undo tablespaces中是不同的(仍然不确定这是否意味着在从旋转中取出之前3是活动的)。为了避免我的不确定性，我创建了两个额外的撤销表空间。
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天前，它被设置为不活动，此后就没有任何变化。
奇怪的是，根据下面的查询，当前面的查询显示为3时，只有2个活动的撤消日志。我是不是遗漏了什么？
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
Server Fault用户
发布于 2020-11-19 12:52:01
在与一个不断增长的撤销表空间斗争了几天之后，我们终于找到了答案，所以我将分享结果：
SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;我们有2/2活动(默认)撤消表空间。1只在1GB左右，另一只在90 1GB左右生长。
根据文档：
位于所选撤消表空间中的回滚段不活动，因此不会分配给新事务。允许当前使用回滚段的现有事务完成。
那部分是关键。据我所知，在清理之前，所有事务都需要完成。
我们查找了所有正在运行的事务：
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分钟内一切都恢复了。所有的仓库都被收回了。
以上两个查询可以结合起来，一步一步地获得信息：
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';```https://serverfault.com/questions/1022863
复制相似问题
腾讯云开发者
