导读
如何查看 DDL 的进度?
使用 MariaDB/Percona 版本的一个便利之处就是可以及时查看 DDL 的进度, 进而预估 DDL 耗时.
其实, 在官方版本里也是可以查看 DDL 进度的, 认真看手册的同学就能发现手册中有提到过:
You can monitor ALTER TABLE progress for InnoDB tables using Performance Schema.
应该怎么做呢, 我们来大概说下.
简言之, 需要启用 performance_schema, 并设置 2 个地方:
- [yejr@imysql]> UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/alter%';
- [yejr@imysql]> UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%';
现在, 跑个 DDL 看看:
- [yejr@imysql]> SELECT COUNT(*) FROM t1;
- +----------+
- | count(*) |
- +----------+
- | 799994 |
- +----------+
- [yejr@imysql]> ALTER TABLE t1 ADD c4 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
在另一个 SESSION 中反复执行下面的 SQL 查看进度:
- [yejr@imysql]> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current;
- +------------------------------------------------------+----------------+----------------+
- | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
- +------------------------------------------------------+----------------+----------------+
- | stage/sql/Sending data | NULL | NULL |
- | stage/innodb/alter table (read PK and internal sort) | 3464 | 31227 |
- +------------------------------------------------------+----------------+----------------+
- [yejr@imysql]> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current;
- +------------------------------------------------------+----------------+----------------+
- | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
- +------------------------------------------------------+----------------+----------------+
- | stage/sql/Sending data | NULL | NULL |
- | stage/innodb/alter table (read PK and internal sort) | 11760 | 31227 |
- +------------------------------------------------------+----------------+----------------+
- [yejr@imysql]> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current;
- +---------------------------------------+----------------+----------------+
- | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
- +---------------------------------------+----------------+----------------+
- | stage/sql/Sending data | NULL | NULL |
- | stage/innodb/alter table (merge sort) | 12888 | 31227 |
- +---------------------------------------+----------------+----------------+
- [yejr@imysql]> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current;
- +-----------------------------------+----------------+----------------+
- | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
- +-----------------------------------+----------------+----------------+
- | stage/sql/Sending data | NULL | NULL |
- | stage/innodb/alter table (insert) | 22432 | 31227 |
- +-----------------------------------+----------------+----------------+
- [yejr@imysql]> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current;
- +----------------------------------+----------------+----------------+
- | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
- +----------------------------------+----------------+----------------+
- | stage/sql/Sending data | NULL | NULL |
- | stage/innodb/alter table (flush) | 34076 | 34980 |
- +----------------------------------+----------------+----------------+
- [yejr@imysql]> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_current;
- +------------------------+----------------+----------------+
- | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
- +------------------------+----------------+----------------+
- | stage/sql/Sending data | NULL | NULL |
- +------------------------+----------------+----------------+
最后, 也可以查看 events_stages_history 里记录的完整过程:
- [yejr@imysql]> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_history;
- +----------------------------------------------------+----------------+----------------+
- | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
- +----------------------------------------------------+----------------+----------------+
- | stage/sql/System lock | NULL | NULL |
- | stage/sql/optimizing | NULL | NULL |
- | stage/sql/statistics | NULL | NULL |
- | stage/sql/preparing | NULL | NULL |
- | stage/sql/executing | NULL | NULL |
- | stage/sql/cleaning up | NULL | NULL |
- | stage/sql/starting | NULL | NULL |
- | stage/sql/checking permissions | NULL | NULL |
- | stage/sql/Opening tables | NULL | NULL |
- | stage/sql/init | NULL | NULL |
- | stage/sql/cleaning up | NULL | NULL |
- | stage/innodb/alter table (log apply table) | 35363 | 35363 |
- | stage/sql/committing alter table to storage engine | NULL | NULL |
- | stage/innodb/alter table (end) | 35363 | 35363 |
- | stage/innodb/alter table (log apply table) | 35747 | 35747 |
- | stage/sql/end | NULL | NULL |
- | stage/sql/query end | NULL | NULL |
- | stage/sql/closing tables | NULL | NULL |
- | stage/sql/freeing items | NULL | NULL |
- | stage/sql/logging slow query | NULL | NULL |
- +----------------------------------------------------+----------------+----------------+
从上面的结果我们也能看到, 一个 DDL 执行过程包括下面几个主要阶段:
stage/innodb/alter table (read PK and internal sort), 读取主键 (聚集索引), 计算需要处理的 data page 数;
stage/innodb/alter table (merge sort), 处理 ALTER TABLE 影响的索引, 每个索引跑一次 (含主键索引);
stage/innodb/alter table (insert), 同上;
stage/innodb/alter table (log apply index), 将执行 DDL 期间新增的 DML 操作应用到 index 上;
stage/innodb/alter table (flush),flush 阶段;
stage/innodb/alter table (log apply table), 将执行 DDL 期间新增的 DML 操作应用到 table 上;
stage/innodb/alter table (end), 收尾阶段.
再说下利用 P_S 查看 ALTER TABLE 进度的局限性:
只支持 MySQL 5.7 + 的版本;
只支持 InnoDB 引擎表;
不支持 spatial indexes.
最后, 我们可以运行下面的 SQL, 查看当前所有 ALTER TABLE 进度及其对应的 DDL SQL:
- [yejr@imysql]> SELECT ec.THREAD_ID, ec.EVENT_NAME, ec.WORK_COMPLETED, ec.WORK_ESTIMATED, pt.STATE, pt.INFO FROM performance_schema.events_stages_current ec left join performance_schema.threads th on ec.thread_id = th.thread_id left join information_schema.PROCESSLIST pt on th.PROCESSLIST_ID = pt.ID where pt.INFO like 'ALTER%'\G
- *************************** 1. row ***************************
- THREAD_ID: 105
- EVENT_NAME: stage/innodb/alter table (merge sort)
- WORK_COMPLETED: 14032
- WORK_ESTIMATED: 33999
- STATE: altering table
- INFO: ALTER TABLE t1 DROP c4
文中案例的 MySQL 版本: 5.7.16.
参考
14.16.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema , https://dev.mysql.com/doc/refman/5.7/en/monitor-alter-table-performance-schema.html
MySQL 5.6 Online DDL 异常分析 http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653929684&idx=1&sn=27d8155fa7be47696675a4512304a95a&chksm=bd3b5abe8a4cd3a8b28684bf4a2e6692b6a86d4ef14aa4b6072a2c8f35e8090545b9a7984cc8&scene=21#wechat_redirect
来源: https://www.qcloud.com/developer/article/1184197