5.7新特性1：查看ddl進(jìn)度

導(dǎo)讀：如何查看DDL的進(jìn)度？

使用MariaDB/Percona版本的一個(gè)便利之處就是可以及時(shí)查看DDL的進(jìn)度，進(jìn)而預(yù)估DDL耗時(shí)。
其實(shí)，在官方版本里也是可以查看DDL進(jìn)度的，認(rèn)真看手冊(cè)的同學(xué)就能發(fā)現(xiàn)手冊(cè)中有提到過：

You can monitor ALTER TABLE progress for InnoDB tables using Performance Schema.

如何開啟

需要啟用performance_schema，并設(shè)置2個(gè)地方：

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

測(cè)試

  • ddl(copy方式)
mysql> select count(1) from sbtest2;
+----------+
| count(1) |
+----------+
|  9998951 |
+----------+
1 row in set (2.10 sec)

mysql> alter table sysbench.sbtest2 ALGORITHM=COPY, add test7 varchar(20) not null default 'test7';

另一個(gè)窗口查看進(jìn)度:

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 |         126959 |        9842742 |
+-----------------------------+----------------+----------------+
1 row in set (0.00 sec)

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 |        4494926 |        9842742 |
+-----------------------------+----------------+----------------+
1 row in set (0.00 sec)

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 |        8398679 |        9842742 |
+-----------------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
Empty set (0.00 sec)
  • ddl(inplace方式)
mysql> alter table sysbench.sbtest2 add test8 varchar(20) not null default 'test8';

另一個(gè)窗口查看

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/innodb/alter table (read PK and internal sort) |         226282 |         703674 |
+------------------------------------------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
+-----------------------------------+----------------+----------------+
| EVENT_NAME                        | WORK_COMPLETED | WORK_ESTIMATED |
+-----------------------------------+----------------+----------------+
| stage/innodb/alter table (insert) |         988006 |        1210970 |
+-----------------------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
+----------------------------------+----------------+----------------+
| EVENT_NAME                       | WORK_COMPLETED | WORK_ESTIMATED |
+----------------------------------+----------------+----------------+
| stage/innodb/alter table (flush) |        1184776 |        1184776 |
+----------------------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
+----------------------------------+----------------+----------------+
| EVENT_NAME                       | WORK_COMPLETED | WORK_ESTIMATED |
+----------------------------------+----------------+----------------+
| stage/innodb/alter table (flush) |        1205192 |        1205192 |
+----------------------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
Empty set (0.00 sec)

最后，也可以查看 events_stages_history 里記錄的完整過程：

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_history; 
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/innodb/alter table (merge sort)                |         896692 |        1210970 |
| stage/innodb/alter table (insert)                    |        1179932 |        1179932 |
| stage/innodb/alter table (flush)                     |        1219397 |        1219397 |
| stage/innodb/alter table (log apply table)           |        1219781 |        1219781 |
| stage/innodb/alter table (end)                       |        1219781 |        1219781 |
| stage/innodb/alter table (log apply table)           |        1220165 |        1220165 |
| stage/innodb/alter table (end)                       |        1154899 |        1154899 |
| stage/innodb/alter table (log apply table)           |        1155283 |        1155283 |
| stage/sql/copy to tmp table                          |        9998951 |        9842742 |
| stage/innodb/alter table (read PK and internal sort) |         566352 |         703674 |
+------------------------------------------------------+----------------+----------------+
10 rows in set (0.00 sec)

一個(gè)ddl的執(zhí)行過程(inplace)

從上面的結(jié)果我們也能看到，一個(gè)DDL執(zhí)行過程包括下面幾個(gè)主要階段：

  1. stage/innodb/alter table (read PK and internal sort)，讀取主鍵（聚集索引），計(jì)算需要處理的data page數(shù)；

  2. stage/innodb/alter table (merge sort)，處理ALTER TABLE影響的索引，每個(gè)索引跑一次（含主鍵索引）；

  3. stage/innodb/alter table (insert)，同上；

  4. stage/innodb/alter table (log apply index)，將執(zhí)行DDL期間新增的DML操作應(yīng)用到index上；

  5. stage/innodb/alter table (flush)，flush階段；

  6. stage/innodb/alter table (log apply table)，將執(zhí)行DDL期間新增的DML操作應(yīng)用到table上；

  7. stage/innodb/alter table (end)，收尾階段。

局限

  • 只支持MySQL 5.7+的版本
  • 只支持InnoDB引擎表；
  • 不支持spatial indexes。

查看當(dāng)前所有ALTER TABLE的進(jìn)度及其對(duì)應(yīng)的ddl sql

mysql>  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: 1258
    EVENT_NAME: stage/innodb/alter table (read PK and internal sort)
WORK_COMPLETED: 11366
WORK_ESTIMATED: 737631
         STATE: altering table
          INFO: alter table sysbench.sbtest2 add test9 varchar(20) not null default 'test9'
*************************** 2. row ***************************
     THREAD_ID: 1314
    EVENT_NAME: stage/innodb/alter table (read PK and internal sort)
WORK_COMPLETED: 22970
WORK_ESTIMATED: 197835
         STATE: altering table
          INFO: alter table sbtest3 add test1 varchar(257) not null default 'test1'
2 rows in set (0.00 sec)
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容