不用MariaDB/Percona也能查看DDL的进度

如何查看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执行过程包括下面几个主要阶段：

再说下利用P_S查看ALTER TABLE 进度的局限性：

最后，我们可以运行下面的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。