MySQL 案例：如何监控DDL

原创

王文安@DBA

修改于 2022-02-22 09:25:32

1.6K0

文章被收录于专栏：腾讯云数据库专家服务腾讯云数据库专家服务

背景

经常会有用户在咨询大表 DDL 的进度，预估时间等信息，其实依靠经验来做判断的话，比较容易出现误差，而且也和评估人的实际评估手段有较大的关系。事实上 MySQL 本身就有 DDL 的监控手段吗，只是默认情况没有进行开启。

实践一下

测试环境使用了腾讯云数据库 MySQL 5.7，官方的 MySQL 8.0，5.7 版本基本同理。首先需要打开performance_schema（腾讯云 MySQL 需要留意是否开启了这个参数），然后在setup_instruments表中开启 alter 操作对应的监控项以及p_f(performance_schema)对应的表。使用如下操作开启：

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/alter%';

测试使用的表：

CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

测试的对象语句：

alter table sbtest.sbtest1 modify c varchar(128)  NOT NULL DEFAULT '';

因为开启了 p_f 的参数，所以现在能在内存表里面看到具体的数据了：

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
+-----------------------------+----------------+----------------+
| EVENT_NAME                  | WORK_COMPLETED | WORK_ESTIMATED |
+-----------------------------+----------------+----------------+
| stage/sql/copy to tmp table |         309523 |        9863083 |
+-----------------------------+----------------+----------------+
1 row in set (0.00 sec)

WORK_COMPLETED 表示已经完成的“工作量”，WORK_ESTIMATED表示预计的总工作量，所以评估 DDL 操作的进度的时候可以用两个指标来判断整体的进度。预估时间的时候，可以参考如下语句进行简单的估算：

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;select sleep(5);SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;

+-----------------------------+----------------+----------------+

| EVENT_NAME                  | WORK_COMPLETED | WORK_ESTIMATED |

+-----------------------------+----------------+----------------+

| stage/sql/copy to tmp table |         385652 |        9863083 |

+-----------------------------+----------------+----------------+

1 row in set (0.00 sec)

+----------+

| sleep(5) |

+----------+

|        0 |

+----------+

1 row in set (5.00 sec)

+-----------------------------+----------------+----------------+

| EVENT_NAME                  | WORK_COMPLETED | WORK_ESTIMATED |

+-----------------------------+----------------+----------------+

| stage/sql/copy to tmp table |         923696 |        9863083 |

+-----------------------------+----------------+----------------+

1 row in set (0.00 sec)

mysql> select 9863083/(923696-385652)*5/60;

+------------------------------+

| 9863083/(923696-385652)*5/60 |

+------------------------------+

|                   1.52761407 |

+------------------------------+

1 row in set (0.00 sec)

简单计算出来的总时间是 1.57 分钟，大概就是 90 秒左右。实际上运行的时间可以参考语句执行的具体时间：

mysql> alter table sbtest.sbtest1 modify c varchar(120)  NOT NULL DEFAULT '';

Query OK, 9999999 rows affected (1 min 37.27 sec)

Records: 9999999  Duplicates: 0  Warnings: 0

不过 DDL 本身操作的时候也有很多的阶段，current 表本身只能看到当前阶段的进度，可以通过定期的查询来估算进度。