不用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执行过程包括下面几个主要阶段:

  1. stage/innodb/alter table (read PK and internal sort),读取主键(聚集索引),计算需要处理的data page数;
  2. stage/innodb/alter table (merge sort),处理ALTER TABLE影响的索引,每个索引跑一次(含主键索引);
  3. stage/innodb/alter table (insert),同上;
  4. stage/innodb/alter table (log apply index),将执行DDL期间新增的DML操作应用到index上;
  5. stage/innodb/alter table (flush),flush阶段;
  6. stage/innodb/alter table (log apply table),将执行DDL期间新增的DML操作应用到table上;
  7. 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。

参考

This post has already been read 847 times!

叶金荣

Oracle MySQL ACE Director,腾讯云TVP成员

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax

Time limit is exhausted. Please reload CAPTCHA.