MDL 锁视图 / DDL 进度显示

大表的 DDL 操作往往会比较耗时，在无法感知整个 DDL 的进行阶段与阶段进度时，可能会产生很大的困扰；MySQL 原生的 performance_schema 功能开启后，会带来一定的性能损失和内存占用。为解决以上问题，云数据库 MySQL 版提供了一种轻量化的 performance_schema 功能，能够在低消耗的前提下提供 MDL 锁视图与 DDL 进度显示的能力。

使用限制

该功能仅在数据库版本为 MySQL 8.0 的实例提供。

使用方法

1. 修改参数 loose_rds_performance_schema 为 ON。

   说明

   • 关于修改参数的详细操作，请参见修改参数。如果您的 MySQL 8.0 实例的可修改参数中没有该参数，请提交工单联系技术支持升级实例。
   • 当 performance_schema = on 时，loose_rds_performance_schema 无效。

2. 获取 MDL 锁视图与 DDL 进度。

   1. 查看 performance_schema.metadata_locks 表以获得当前系统中 MDL 锁使用情况。

   MySQL [****]> select * from performance_schema.processlist;
   +----+-----------------+---------------------+--------+---------+------+---------------------------------+----------------------------------------------------------+---------+-----------+---------------+------------------+
   | ID | USER            | HOST                | DB     | COMMAND | TIME | STATE                           | INFO                                                     | TIME_MS | ROWS_SENT | ROWS_EXAMINED | EXECUTION_ENGINE |
   +----+-----------------+---------------------+--------+---------+------+---------------------------------+----------------------------------------------------------+---------+-----------+---------------+------------------+
   |  5 | event_scheduler | localhost           | NULL   | Daemon  | 1925 | Waiting on empty queue          | NULL                                                     | 1925356 |         0 |             0 | PRIMARY          |
   | 13 | root            | 127.***.***.1:39294 | testDB | Query   |    0 | executing                       | select * from performance_schema.processlist             |       0 |         1 |             4 | PRIMARY          |
   | 15 | root            | 127.***.***.1:56616 | testDB | Query   |  208 | Waiting for table metadata lock | alter table T add column d varchar(10),algorithm=inplace |  208006 |         9 |            29 | PRIMARY          |
   +----+-----------------+-----------------+------+---------+------+---------------------------------+----------------------------------------------------------+---------+-----------+---------------+------------------+
   
   MySQL [****]> select OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, OWNER_THREAD_ID from performance_schema.metadata_locks;
   +--------------------+----------------+---------------------+-------------+-----------------+
   | OBJECT_SCHEMA      | OBJECT_NAME    | LOCK_TYPE           | LOCK_STATUS | OWNER_THREAD_ID |
   +--------------------+----------------+---------------------+-------------+-----------------+
   | NULL               | NULL           | INTENTION_EXCLUSIVE | GRANTED     |              84 |
   | NULL               | NULL           | INTENTION_EXCLUSIVE | GRANTED     |              84 |
   | testDB             | NULL           | INTENTION_EXCLUSIVE | GRANTED     |              84 |
   | testDB             | T              | SHARED_UPGRADABLE   | GRANTED     |              84 |
   | NULL               | NULL           | INTENTION_EXCLUSIVE | GRANTED     |              84 |
   | NULL               | testDB/T       | INTENTION_EXCLUSIVE | GRANTED     |              84 |
   | testDB             | #sql-1aafcb_f  | EXCLUSIVE           | GRANTED     |              84 |
   | testDB             | T              | EXCLUSIVE           | PENDING     |              84 |
   | performance_schema | processlist    | SHARED_READ         | GRANTED     |              80 |
   | performance_schema | metadata_locks | SHARED_READ         | GRANTED     |              80 |
   | testDB             | T              | SHARED_READ         | GRANTED     |              80 |
   +--------------------+----------------+---------------------+-------------+-----------------+
   11 rows in set (0.00 sec)
   

   2. 查看 performance_schema.events_stages_current 表以获得当前 DDL 执行进度。

      • 在 DDL 执行过程中，可以查看 events_stages_current 表获取 DDL 的执行状态和进度。

        MySQL [****]> SELECT THREAD_ID,EVENT_ID, EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,(WORK_COMPLETED/WORK_ESTIMATED)*100 as PROGRESS FROM performance_schema.events_stages_current;
        +-----------+----------+------------------------------------------------------+----------------+----------------+----------+
        | THREAD_ID | EVENT_ID | EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED | PROGRESS |
        +-----------+----------+------------------------------------------------------+----------------+----------------+----------+
        |        84 |        2 | stage/innodb/alter table (read PK and internal sort) |         115952 |         312538 |  37.1001 |
        +-----------+----------+------------------------------------------------------+----------------+----------------+----------+
        1 row in set (0.00 sec)
        

      • 在 DDL 执行过程中，可以借助 threads 和 processlist 表查看当前事件对应的 SQL。

        MySQL [****]> SELECT esc.THREAD_ID, esc.EVENT_NAME, esc.WORK_COMPLETED, esc.WORK_ESTIMATED, pl.INFO FROM performance_schema.events_stages_current esc LEFT JOIN performance_schema.threads th ON esc.thread_id = th.thread_id LEFT JOIN performance_schema.processlist pl ON th.PROCESSLIST_ID = pl.ID;
        +-----------+------------------------------------------------------+----------------+----------------+----------------------------------------------------------+
        | THREAD_ID | EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED | INFO                                                     |
        +-----------+------------------------------------------------------+----------------+----------------+----------------------------------------------------------+
        |        84 | stage/innodb/alter table (read PK and internal sort) |         150660 |         312538 | alter table T add column d varchar(10),algorithm=inplace |
        +-----------+------------------------------------------------------+----------------+----------------+----------------------------------------------------------+
        1 row in set (0.11 sec)