8.0.25 mysqldump因分区过多导致导出数据缓慢
问题描述
8.0.25 mysqldump导出数据迟迟不结束
环境信息
- mysqldump阻塞状态
2. mysqldump的general log
3.执行缓慢SQL
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
mysql>
SELECT LOGFILE_GROUP_NAME,
FILE_NAME,
TOTAL_EXTENTS,
INITIAL_SIZE,
ENGINE,
EXTRA
FROM INFORMATION_SCHEMA.FILES
WHERE FILE_TYPE = 'UNDO LOG'
AND FILE_NAME IS NOT NULL
AND LOGFILE_GROUP_NAME IS NOT NULL
AND LOGFILE_GROUP_NAME IN
(SELECT DISTINCT LOGFILE_GROUP_NAME
FROM INFORMATION_SCHEMA.FILES
WHERE FILE_TYPE = 'DATAFILE'
AND TABLESPACE_NAME IN
(SELECT DISTINCT TABLESPACE_NAME
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA IN ('cbps_pay_rz_02_01_b')))
GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE
ORDER BY LOGFILE_GROUP_NAME;