mysql 8.0 下undo过大问题处理
参考：
https://zhuanlan.zhihu.com/p/448822039
https://www.cnblogs.com/zmc60/p/16461863.html
各个列的含义：
①.id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
②.user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
③.host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
④.db列，显示这个进程目前连接的是哪个数据库
⑤.command列，显示当前连接的执行的命令，一般取值为休眠(sleep)，查询(query)，连接(connect)等
⑥.time列，显示这个状态持续的时间，单位是秒
⑦.state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
⑧.info列，显示这个sql语句，是判断问题语句的一个重要依据
在主从复制环境中，show processlist或show full processlist对于判断状态很有帮助，例如下面的state列：
处理过程：
show global variables like "%datadir%";
+---------------+----------------------+
| Variable_name | Value |
+---------------+----------------------+
| datadir | /mysql/data/ |
+---------------+----------------------+
1 row in set (0.00 sec)
mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG';
+-----------------+------------+
| TABLESPACE_NAME | FILE_NAME |
+-----------------+------------+
| innodb_undo_001 | ./undo_001 |
| innodb_undo_002 | ./undo_002 |
+-----------------+------------+
2 rows in set (0.00 sec)
mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME | STATE |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | active |
+-----------------+--------+
2 rows in set (0.00 sec)
mysql> SHOW STATUS LIKE 'Innodb_undo_tablespaces%';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| Innodb_undo_tablespaces_total | 2 |
| Innodb_undo_tablespaces_implicit | 2 |
| Innodb_undo_tablespaces_explicit | 0 |
| Innodb_undo_tablespaces_active | 2 |
+----------------------------------+-------+
4 rows in set (0.01 sec)
mysql> show variables like '%truncate%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 128 |
| innodb_undo_log_truncate | ON |
+--------------------------------------+-------+
2 rows in set (0.01 sec)
mysql> select * from INFORMATION_SCHEMA.INNODB_TRX;
Empty set (0.00 sec)
mysql> show full processlist;
mysql> exit
Bye
[root@mysql1 ~]# cd /mysql/data
[root@mysql1 data]# ll undo*
-rw-r-----. 1 mysql mysql 16777216 Dec 16 14:40 undo_001
-rw-r-----. 1 mysql mysql 16777216 Dec 16 14:40 undo_002
[root@mysql1 data]# mysql -u root -pabcd1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.31 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
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 |
+--------------------------+------------+
5 rows in set (0.00 sec)
mysql> system du -sh /mysql/data/undo*
16M /mysql/data/undo_001
16M /mysql/data/undo_002
mysql> create undo tablespace undo003 add datafile '/mysql/data/undo003.ibu';
Query OK, 0 rows affected (0.68 sec)
mysql> system du -sh /mysql/data/undo*
16M /mysql/data/undo_001
16M /mysql/data/undo_002
16M /mysql/data/undo003.ibu
mysql> select * from information_schema.INNODB_TABLESPACES where name like '%undo%';
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
| SPACE | NAME | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
| 4294967279 | innodb_undo_001 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 16777216 | 0 | 8.0.31 | 1 | N | active |
| 4294967278 | innodb_undo_002 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 16777216 | 0 | 8.0.31 | 1 | N | active |
| 4294967277 | undo003 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 16777216 | 0 | 8.0.31 | 1 | N | active |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
3 rows in set (0.01 sec)
mysql> alter undo tablespace innodb_undo_002 set inactive;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from information_schema.INNODB_TABLESPACES where name like '%undo%';
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
| SPACE | NAME | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
| 4294967279 | innodb_undo_001 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 16777216 | 0 | 8.0.31 | 1 | N | active |
| 4294967151 | innodb_undo_002 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 16777216 | 0 | 8.0.31 | 1 | N | empty |
| 4294967277 | undo003 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 16777216 | 0 | 8.0.31 | 1 | N | active |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
3 rows in set (0.00 sec)
mysql> system du -sh /mysql/data/undo*
16M /mysql/data/undo_001
16M /mysql/data/undo_002
16M /mysql/data/undo003.ibu
mysql> alter undo tablespace innodb_undo_002 set active;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'innodb_undo_tablespaces';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_undo_tablespaces | 2 |
+-------------------------+-------+
1 row in set (0.00 sec)
mysql> alter undo tablespace undo003 set inactive;
Query OK, 0 rows affected (0.01 sec)
mysql> drop undo tablespace undo003;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from information_schema.INNODB_TABLESPACES where name like '%undo%';
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
| SPACE | NAME | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
| 4294967279 | innodb_undo_001 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 16777216 | 0 | 8.0.31 | 1 | N | active |
| 4294967151 | innodb_undo_002 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 16777216 | 0 | 8.0.31 | 1 | N | active |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+-----------------+----------------+---------------+------------+--------+
2 rows in set (0.00 sec)
启用监控：
mysql> SELECT NAME, SUBSYSTEM, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%truncate%';
+-----------------------------------+-----------+------------------------------------------------------------------------+
| NAME | SUBSYSTEM | COMMENT |
+-----------------------------------+-----------+------------------------------------------------------------------------+
| purge_truncate_history_count | purge | Number of times the purge thread attempted to truncate undo history |
| purge_truncate_history_usec | purge | Time (in microseconds) the purge thread spent truncating undo history. |
| undo_truncate_count | undo | Number of times undo truncation was initiated |
| undo_truncate_start_logging_count | undo | Number of times during undo truncation a log file was started |
| undo_truncate_done_logging_count | undo | Number of times during undo truncation a log file was deleted |
| undo_truncate_usec | undo | Time (in microseconds) spent to process undo truncation |
+-----------------------------------+-----------+------------------------------------------------------------------------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="undo_truncate_count"\G
*************************** 1. row ***************************
NAME: undo_truncate_count
SUBSYSTEM: undo
COUNT: 0
MAX_COUNT: NULL
MIN_COUNT: NULL
AVG_COUNT: NULL
COUNT_RESET: 0
MAX_COUNT_RESET: NULL
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: NULL
TIME_DISABLED: NULL
TIME_ELAPSED: NULL
TIME_RESET: NULL
STATUS: disabled
TYPE: counter
COMMENT: Number of times undo truncation was initiated
1 row in set (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="purge_truncate_history_count"\G
*************************** 1. row ***************************
NAME: purge_truncate_history_count
SUBSYSTEM: purge
COUNT: 0
MAX_COUNT: NULL
MIN_COUNT: NULL
AVG_COUNT: NULL
COUNT_RESET: 0
MAX_COUNT_RESET: NULL
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: NULL
TIME_DISABLED: NULL
TIME_ELAPSED: NULL
TIME_RESET: NULL
STATUS: disabled
TYPE: counter
COMMENT: Number of times the purge thread attempted to truncate undo history
1 row in set (0.00 sec)
mysql> SET GLOBAL innodb_monitor_enable = undo_truncate_count;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="undo_truncate_count"\G
*************************** 1. row ***************************
NAME: undo_truncate_count
SUBSYSTEM: undo
COUNT: 0
MAX_COUNT: NULL
MIN_COUNT: NULL
AVG_COUNT: 0
COUNT_RESET: 0
MAX_COUNT_RESET: NULL
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2022-12-22 19:07:34
TIME_DISABLED: NULL
TIME_ELAPSED: 5
TIME_RESET: NULL
STATUS: enabled
TYPE: counter
COMMENT: Number of times undo truncation was initiated
1 row in set (0.00 sec)
mysql> SET GLOBAL innodb_monitor_enable = purge_truncate_history_count;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="undo_truncate_count"\G
*************************** 1. row ***************************
NAME: undo_truncate_count
SUBSYSTEM: undo
COUNT: 0
MAX_COUNT: NULL
MIN_COUNT: NULL
AVG_COUNT: 0
COUNT_RESET: 0
MAX_COUNT_RESET: NULL
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2022-12-22 19:07:34
TIME_DISABLED: NULL
TIME_ELAPSED: 38
TIME_RESET: NULL
STATUS: enabled
TYPE: counter
COMMENT: Number of times undo truncation was initiated
1 row in set (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="purge_truncate_history_count"\G
*************************** 1. row ***************************
NAME: purge_truncate_history_count
SUBSYSTEM: purge
COUNT: 0
MAX_COUNT: NULL
MIN_COUNT: NULL
AVG_COUNT: 0
COUNT_RESET: 0
MAX_COUNT_RESET: NULL
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2022-12-22 19:08:06
TIME_DISABLED: NULL
TIME_ELAPSED: 42
TIME_RESET: NULL
STATUS: enabled
TYPE: counter
COMMENT: Number of times the purge thread attempted to truncate undo history
1 row in set (0.01 sec)
mysql> SET GLOBAL innodb_monitor_disable = undo_truncate_count;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="undo_truncate_count"\G
*************************** 1. row ***************************
NAME: undo_truncate_count
SUBSYSTEM: undo
COUNT: 0
MAX_COUNT: NULL
MIN_COUNT: NULL
AVG_COUNT: 0
COUNT_RESET: 0
MAX_COUNT_RESET: NULL
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2022-12-22 19:07:34
TIME_DISABLED: 2022-12-22 19:09:07
TIME_ELAPSED: 93
TIME_RESET: NULL
STATUS: disabled
TYPE: counter
COMMENT: Number of times undo truncation was initiated
1 row in set (0.00 sec)
mysql>
查看表空间：
mysql> SELECT NAME, FILE_SIZE/1024/1024/1024 AS size_in_gb FROM information_schema.INNODB_TABLESPACES ORDER BY FILE_SIZE;
+------------------+----------------+
| NAME | size_in_gb |
+------------------+----------------+
| sys/sys_config | 0.000106811523 |
| jycdb/test | 0.000106811523 |
| t/t | 0.000106811523 |
| jyc/t | 0.000106811523 |
| innodb_temporary | 0.011718750000 |
| innodb_undo_001 | 0.015625000000 |
| innodb_undo_002 | 0.015625000000 |
| undo003 | 0.015625000000 |
| mysql | 0.023437500000 |
+------------------+----------------+
9 rows in set (0.00 sec)
mysql> SELECT * FROM information_schema.INNODB_SESSION_TEMP_TABLESPACES;
+----+------------+----------------------------+-------+----------+-----------+
| ID | SPACE | PATH | SIZE | STATE | PURPOSE |
+----+------------+----------------------------+-------+----------+-----------+
| 8 | 4243767290 | ./#innodb_temp/temp_10.ibt | 81920 | ACTIVE | INTRINSIC |
| 0 | 4243767281 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767282 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767283 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767284 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767285 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767286 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767287 | ./#innodb_temp/temp_7.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767288 | ./#innodb_temp/temp_8.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767289 | ./#innodb_temp/temp_9.ibt | 81920 | INACTIVE | NONE |
+----+------------+----------------------------+-------+----------+-----------+
10 rows in set (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO;
Empty set (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary';
+------------+-----------+-----------+------------------+---------------+--------------+------------+--------------------+----------------------+--------+---------------+--------------+--------------+--------------+---------------+-------------+--------------+--------------+-----------------+---------------+------------------+------------------+--------------+---------------------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+-------------+-------------+------------+----------+--------+-------+
| FILE_ID | FILE_NAME | FILE_TYPE | TABLESPACE_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | LOGFILE_GROUP_NAME | LOGFILE_GROUP_NUMBER | ENGINE | FULLTEXT_KEYS | DELETED_ROWS | UPDATE_COUNT | FREE_EXTENTS | TOTAL_EXTENTS | EXTENT_SIZE | INITIAL_SIZE | MAXIMUM_SIZE | AUTOEXTEND_SIZE | CREATION_TIME | LAST_UPDATE_TIME | LAST_ACCESS_TIME | RECOVER_TIME | TRANSACTION_COUNTER | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME | UPDATE_TIME | CHECK_TIME | CHECKSUM | STATUS | EXTRA |
+------------+-----------+-----------+------------------+---------------+--------------+------------+--------------------+----------------------+--------+---------------+--------------+--------------+--------------+---------------+-------------+--------------+--------------+-----------------+---------------+------------------+------------------+--------------+---------------------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+-------------+-------------+------------+----------+--------+-------+
| 4294967293 | ./ibtmp1 | TEMPORARY | innodb_temporary | | NULL | NULL | NULL | NULL | InnoDB | NULL | NULL | NULL | 2 | 12 | 1048576 | 12582912 | NULL | 67108864 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 6291456 | NULL | NULL | NULL | NULL | NORMAL | NULL |
+------------+-----------+-----------+------------------+---------------+--------------+------------+--------------------+----------------------+--------+---------------+--------------+--------------+--------------+---------------+-------------+--------------+--------------+-----------------+---------------+------------------+------------------+--------------+---------------------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+-------------+-------------+------------+----------+--------+-------+
1 row in set (0.01 sec)
https://dev.mysql.com/doc/refman/8.0/en/information-schema-innodb-tablespaces-table.html
类似问题，解决：删掉长事务。
https://serverfault.com/questions/1022863/mysql-8-undo-log-not-truncating-after-excessive-growth
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;
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';
select concat('KILL ',id,';') from information_schema.processlist where user != 'system_user' and db = 'xxx';
最后修改时间：2022-12-26 13:12:02
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
