Docs

RDS for MySQL

Feature

MDL lock view/DDL progress display

DDL operations on large tables are often time-consuming. It can be problematic when you are unable to track the progress and stages of the entire DDL process. Enabling the native MySQL performance_schema feature causes performance degradation and increased memory usage. To solve these problems, RDS for MySQL provides a lightweight performance_schema feature that offers MDL lock view and DDL progress display capabilities with low overhead.

Supported version

MySQL 5.7 version, and the kernel minor version is MySQL 5.7.44_20240815 and above.

MySQL 8.0 version, and the kernel minor version is MySQL 8.0.32_20230815 and above.

MySQL 8.4 version, and the kernel minor version is MySQL 8.4.7_20260415 and above.

tip

You can view the kernel minor version of an instance by Viewing instance information. If the kernel minor version of your instance is not within the supported kernel minor version range, you can Manually upgrading the instance kernel minor version.

Usage

Modify the loose_rds_performance_schema parameter to ON.

tip

For more information about how to modify parameters, refer to Modifying parameters. If this parameter is not available in the modifiable parameters for your instance, submit a ticket to contact technical support to upgrade the instance.

When performance_schema = on, loose_rds_performance_schema is invalid.

Obtain the MDL lock view and DDL progress.

Check the performance_schema.metadata_locks table to view the MDL lock usage of the current system.

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)

Check the performance_schema.events_stages_current table to view the current DDL execution progress.

During DDL execution, you can check the events_stages_current table to obtain the execution state and progress of the 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)

During DDL execution, you can use the threads and processlist tables to view the SQL for the current event.

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)

Subsequent steps

Compared to the native MySQL performance_schema, the performance_schema feature provided by RDS for MySQL is a lightweight implementation that reduces instance resource consumption. However, this feature can still put significant pressure on instances with low specifications. Therefore, after you enable this feature, you must continuously monitor the memory usage rate of the instance.

RDS for MySQL provides a Memory usage rate monitoring item. You can configure an alert policy for this item and set an alert threshold for your instance's memory usage rate. When the memory usage rate exceeds the threshold, you will receive a timely alert message. For more information about creating alert policies, refer to Creating alert policies.

RDS for MySQL provides the modifiable parameter performance_schema_max_thread_instances. This parameter sets the maximum number of observed threads, which is the maximum number of rows in the performance_schema.threads table, to control the memory consumption of the performance_schema feature. The default value of this parameter is 1024, and you can adjust it based on your instance specification and business requirements. For more information about modifying parameters, see Modifying parameters.

tip

This parameter must be used with loose_rds_performance_schema and takes effect only when loose_rds_performance_schema is set to ON.

When the performance_schema_max_thread_instances parameter value is 1024, high concurrency may cause the loss of MDL lock records in the performance_schema.metadata_locks table. In this case, consider modifying the value of performance_schema_max_thread_instances to -1. This removes the limit on memory consumption for the performance_schema feature.

You can also evaluate your business requirements to determine whether to continue using this feature. If you no longer need this feature, modify the loose_rds_performance_schema parameter to OFF to disable it.

Last updated: 2026.05.28 15:17:48