在线监控 DDL 进度

在线监控 DDL 进度

1、开启 PS 统计

mysql> use performance_schema;
Database changed
mysql> select * from setup_instruments where name like 'stage/innodb/alter%';
+------------------------------------------------------+---------+-------+
| NAME                                                 | ENABLED | TIMED |
+------------------------------------------------------+---------+-------+
| stage/innodb/alter table (end)                       | YES     | YES   |
| stage/innodb/alter table (flush)                     | YES     | YES   |
| stage/innodb/alter table (insert)                    | YES     | YES   |
| stage/innodb/alter table (log apply index)           | YES     | YES   |
| stage/innodb/alter table (log apply table)           | YES     | YES   |
| stage/innodb/alter table (merge sort)                | YES     | YES   |
| stage/innodb/alter table (read PK and internal sort) | YES     | YES   |
+------------------------------------------------------+---------+-------+
7 rows in set (0.00 sec)

mysql> update setup_instruments set ENABLED='YES' where NAME like 'stage/innodb/alter%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 7  Changed: 0  Warnings: 0

mysql> select * from setup_consumers where name like '%stages%';
+----------------------------+---------+
| NAME                       | ENABLED |
+----------------------------+---------+
| events_stages_current      | NO      |
| events_stages_history      | NO      |
| events_stages_history_long | NO      |
+----------------------------+---------+
3 rows in set (0.00 sec)

mysql> update setup_consumers set ENABLED='YES' where NAME like '%stages%%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql>

2、在表 test.t2 添加一个字段

mysql> alter table test.t2 add `t_name` varchar(20) after id;
Query OK, 0 rows affected (12.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

3、开启第二个回话监控 DDL 进度

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) |           4819 |          50952 |
+------------------------------------------------------+----------------+----------------+
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 (read PK and internal sort) |          50196 |          50952 |
+------------------------------------------------------+----------------+----------------+
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 (read PK and internal sort) |          54720 |          54720 |
+------------------------------------------------------+----------------+----------------+
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 (read PK and internal sort) |          65490 |          65490 |
+------------------------------------------------------+----------------+----------------+
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) |          70363 |          70929 |
+----------------------------------+----------------+----------------+
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)