You need to enable JavaScript to run this app.
导航
MDL 锁视图 / DDL 进度显示
最近更新时间:2024.01.09 11:24:34首次发布时间:2023.06.14 15:10:25

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

使用限制

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

使用方法

  1. 修改参数 loose_rds_performance_schemaON

    说明

    • 关于修改参数的详细操作,请参见修改参数。如果您的 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)
    
    1. 查看 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 执行过程中,可以借助 threadsprocesslist 表查看当前事件对应的 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)