All Products
Search

This Product

    PolarDB:View the DDL statement execution status and MDL status

    Document Center

    PolarDB:View the DDL statement execution status and MDL status

    Last Updated:Mar 28, 2026

    PolarDB for MySQL includes Polar performance schema, a lightweight monitoring feature for tracking Data Definition Language (DDL) statement progress and metadata lock (MDL) status. Compared to MySQL's Performance Schema, Polar performance schema uses less memory and imposes lower performance overhead.

    Note

    Polar performance schema applies only to tables that use the InnoDB storage engine. Enabling MySQL's Performance Schema automatically disables Polar performance schema.

    Prerequisites

    Before you begin, ensure that you have:

    • A PolarDB for MySQL cluster running one of the following database engine versions:

      • PolarDB for MySQL 5.7, revision version 5.7.1.0.35 or later

      • PolarDB for MySQL 8.0.1, revision version 8.0.1.1.21 or later

      • PolarDB for MySQL 8.0.2, revision version 8.0.2.2.8 or later

    • Access to the primary node. DDL statements execute only on the primary node. Depending on how you connect:

    To check your database engine version, see Query the engine version.

    Step 1: Enable Polar performance schema

    Set the loose_polar_performance_schema parameter to ON in the PolarDB console. The parameter takes effect after you restart the cluster. For instructions, see Configure cluster and node parameters.

    The following table describes the Polar performance schema parameters.

    ParameterDescription
    loose_polar_performance_schemaEnables or disables Polar performance schema. Valid values: ON, OFF.
    loose_polar_performance_schema_enable_row_locksEnables row-level lock monitoring data collection. Valid values: OFF (default), ON. Available only in PolarDB for MySQL 8.0.1 and 8.0.2.
    performance_schema_max_thread_instancesMaximum number of threads tracked. Valid values: -1 to 65536. Set to -1 to auto-adjust. Do not modify this parameter unless instructed.
    performance_schema_max_metadata_locksMaximum number of MDLs monitored. Valid values: -1 to 1048576. Set to -1 to auto-adjust. Do not modify this parameter unless instructed.

    After restarting the cluster, run the following statement to confirm the feature is enabled:

    SHOW VARIABLES LIKE 'polar_performance_schema';

    Expected output:

    +--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| polar_performance_schema | ON    |
+--------------------------+-------+
1 row in set (0.00 sec)

    Step 2: View DDL execution progress

    Query the performance_schema.events_stages_current table to see the current DDL stage and progress:

    SELECT THREAD_ID, EVENT_ID, EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,
       (WORK_COMPLETED/WORK_ESTIMATED)*100 AS PROGRESS
FROM performance_schema.events_stages_current;

    Sample output:

    +-----------+----------+------------------------------------------------------+----------------+----------------+----------+
| THREAD_ID | EVENT_ID | EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED | PROGRESS |
+-----------+----------+------------------------------------------------------+----------------+----------------+----------+
|   3057989 |       13 | stage/innodb/alter table (read PK and internal sort) |          56634 |         330135 |  17.1548 |
+-----------+----------+------------------------------------------------------+----------------+----------------+----------+
1 row in set (0.00 sec)

    To see the full SQL statement behind the current DDL event, join across performance_schema.threads and information_schema.PROCESSLIST:

    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 information_schema.PROCESSLIST pl ON th.PROCESSLIST_ID = pl.ID;

    Sample output:

    +-----------+------------------------------------------------------+----------------+----------------+-----------------------------------------------------------------------------------------+
| THREAD_ID | EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED | INFO                                                                                    |
+-----------+------------------------------------------------------+----------------+----------------+-----------------------------------------------------------------------------------------+
|   3057989 | stage/innodb/alter table (read PK and internal sort) |          77034 |         330519 | ALTER TABLE test.test ALGORITHM=INPLACE, ADD testA VARCHAR(20) NOT NULL DEFAULT 'testA' |
+-----------+------------------------------------------------------+----------------+----------------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

    Step 3: View MDL status

    Query performance_schema.metadata_locks to see all active metadata locks in the cluster:

    SELECT * FROM performance_schema.metadata_locks;

    Sample output:

    +-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME      | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE                 | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
| GLOBAL      | NULL               | NULL             | NULL        |       139949462878336 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:3103       |         3055785 |              1 |
| TABLE       | test               | test             | NULL        |       139931318980224 | SHARED_WRITE        | TRANSACTION   | GRANTED     | sql_parse.cc:6479      |         3055785 |              1 |
| COMMIT      | NULL               | NULL             | NULL        |       139931318980480 | INTENTION_EXCLUSIVE | EXPLICIT      | GRANTED     | handler.cc:1669        |         3055785 |              1 |
| TABLE       | performance_schema | metadata_locks   | NULL        |       139934227366144 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6479      |         3057612 |              1 |
| GLOBAL      | NULL               | NULL             | NULL        |       139934216849664 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5519       |         3057989 |             13 |
| SCHEMA      | test               | NULL             | NULL        |       139934216849408 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5506       |         3057989 |             13 |
| TABLE       | test               | test             | NULL        |       139934216848640 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     | sql_parse.cc:6479      |         3057989 |             13 |
| BACKUP LOCK | NULL               | NULL             | NULL        |       139934216849280 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5526       |         3057989 |             13 |
| TABLESPACE  | NULL               | test/test        | NULL        |       139934216848384 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | lock.cc:815            |         3057989 |             13 |
| TABLE       | test               | #sql-17d9_2ea89a | NULL        |       139934216848896 | EXCLUSIVE           | STATEMENT     | GRANTED     | sql_table.cc:15054     |         3057989 |             13 |
| GLOBAL      | NULL               | NULL             | NULL        |       139934216850176 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | dictionary_impl.cc:416 |         3057989 |             13 |
| TABLESPACE  | NULL               | test/test        | NULL        |       139934216849920 | EXCLUSIVE           | TRANSACTION   | GRANTED     | dictionary_impl.cc:397 |         3057989 |             13 |
+-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
12 rows in set (0.00 sec)

    Use the OWNER_THREAD_ID value to look up thread details:

    SELECT * FROM performance_schema.threads
WHERE THREAD_ID = "<OWNER_THREAD_ID from metadata_locks>";

    Diagnose and resolve DDL blockage

    DDL operations can be blocked by MDLs held on the primary node or read-only nodes. The following sections cover the two most common blocking scenarios.

    Diagnose DDL blocked by "Waiting for table metadata lock"

    When this happens, SHOW PROCESSLIST shows the DDL in the Waiting for table metadata lock state.

    Step 1: Run SHOW PROCESSLIST on the primary node. The following example uses the force_node hint to route to a specific node:

    /*force_node='pi-bp10k7631d6k3****'*/ SHOW PROCESSLIST;

    Sample output (showing the blocked DDL):

    +-----------+---------+-----------------------+------+----------------+---------+---------------------------------+-------------------------------------------------------------+
| Id        | User    | Host                  | db   | Command        | Time    | State                           | Info                                                        |
+-----------+---------+-----------------------+------+----------------+---------+---------------------------------+-------------------------------------------------------------+
|   3067041 | zyg_root| 172.17.XX.XX:48594    | test | Query          |      22 | Waiting for table metadata lock | alter table t1 add column d varchar(10),algorithm = inplace |
|   3067443 | zyg_root| 172.17.XX.XX:48602    | test | Sleep          |      27 |                                 | NULL                                                        |
...
+-----------+---------+-----------------------+------+----------------+---------+---------------------------------+-------------------------------------------------------------+

    Step 2: The DDL statement (process ID 3067041) is stuck. Query performance_schema.metadata_locks to find what is blocking it:

    /*force_node='pi-bp10k7631d6k3****'*/ SELECT * FROM performance_schema.metadata_locks;

    Sample output:

    +-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME      | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE             | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
| TABLE       | performance_schema | metadata_locks   | NULL        |       139742994307712 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:7688  |         3810041 |              1 |
| TABLE       | test               | t1               | NULL        |       139742992122240 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:7688  |         3810574 |              1 |
| GLOBAL      | NULL               | NULL             | NULL        |       139742992172544 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5637   |         3810086 |              3 |
| SCHEMA      | test               | NULL             | NULL        |       139742993150592 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5624   |         3810086 |              3 |
| TABLE       | test               | t1               | NULL        |       139742993150848 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     | sql_parse.cc:7688  |         3810086 |              3 |
| BACKUP LOCK | NULL               | NULL             | NULL        |       139742993844096 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5644   |         3810086 |              3 |
| TABLESPACE  | NULL               | test/t1          | NULL        |       139742991805696 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | lock.cc:815        |         3810086 |              3 |
| TABLE       | test               | #sql-1b34_2ecca1 | NULL        |       139742992091136 | EXCLUSIVE           | STATEMENT     | GRANTED     | sql_table.cc:15532 |         3810086 |              3 |
| TABLE       | test               | t1               | NULL        |       140266021234688 | EXCLUSIVE           | TRANSACTION   | PENDING     | mdl.cc:4124        |         3810086 |              3 |
+-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
9 rows in set (0.00 sec)

    Thread 3810574 holds a SHARED_READ lock on test/t1, preventing thread 3810086 from acquiring the EXCLUSIVE lock it needs.

    Step 3: Query performance_schema.threads to identify the responsible session:

    /*force_node='pi-bp10k7631d6k3****'*/ SELECT * FROM performance_schema.threads
WHERE THREAD_ID IN (3810086, 3810574)\G

    Sample output:

    *************************** 1. row ***************************
          THREAD_ID: 3810086
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
         PROCESSLIST_ID: 3067041
       PROCESSLIST_USER: zyg_root
       PROCESSLIST_HOST: 172.17.28.253
         PROCESSLIST_DB: test
    PROCESSLIST_COMMAND: Query
       PROCESSLIST_TIME: 41
      PROCESSLIST_STATE: Waiting for table metadata lock
       PROCESSLIST_INFO: alter table t1 add column d varchar(10),algorithm = inplace
...
*************************** 2. row ***************************
          THREAD_ID: 3810574
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
         PROCESSLIST_ID: 3067443
       PROCESSLIST_USER: zyg_root
       PROCESSLIST_HOST: 172.17.28.253
         PROCESSLIST_DB: test
    PROCESSLIST_COMMAND: Sleep
       PROCESSLIST_TIME: 46
      PROCESSLIST_STATE: NULL
       PROCESSLIST_INFO: NULL
...
2 rows in set (0.01 sec)

    The result shows that the blocked DDL operation runs on the 3810086 thread, and a slow query runs on the 3810574 thread. The 3810574 thread holds the lock. Wait for it to commit, or terminate it with KILL 3067443. Then re-execute the ALTER TABLE statement.

    Diagnose DDL blocked by "Wait for syncing with replicas"

    When this happens, SHOW PROCESSLIST on the primary node shows the DDL in the Wait for syncing with replicas state.

    Step 1: Run SHOW PROCESSLIST on the primary node:

    /*force_node='pi-bp10k7631d6k3****'*/ SHOW PROCESSLIST;

    Sample output (showing the blocked DDL):

    +-----------+---------+----------------------+------+----------------+---------+--------------------------------+-------------------------------------------------------------+
| Id        | User    | Host                 | db   | Command        | Time    | State                          | Info                                                        |
+-----------+---------+----------------------+------+----------------+---------+--------------------------------+-------------------------------------------------------------+
|   3067041 | zyg_root| 172.17.28.253:48594  | test | Query          |       6 | Wait for syncing with replicas | alter table t1 add column d varchar(10),algorithm = inplace |
...
+-----------+---------+----------------------+------+----------------+---------+--------------------------------+-------------------------------------------------------------+

    Step 2: Use a hint to query performance_schema.metadata_locks on the specific read-only node:

    /*force_node='pi-bp186ko4o21wl****'*/ SELECT * FROM performance_schema.metadata_locks;

    Sample output:

    +-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE             | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
| TABLE       | test               | t1             | NULL        |       139394298895872 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:7688  |         3513381 |              1 |
| TABLE       | test               | t1             | NULL        |       139394298602240 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:7688  |         3519277 |              1 |
| TABLE       | test               | t1             | NULL        |       139917548369664 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:7688  |         3519279 |              1 |
| TABLE       | test               | t1             | NULL        |       139394296661888 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:7688  |         3519278 |              1 |
| TABLE       | test               | t1             | NULL        |       139394297595520 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:7688  |         3519276 |              1 |
| SCHEMA      | test               | NULL           | NULL        |       139464322084864 | INTENTION_EXCLUSIVE | EXPLICIT      | GRANTED     | sql_table.cc:17404 |              57 |              1 |
| TABLE       | test               | t1             | NULL        |       139464322084992 | EXCLUSIVE           | EXPLICIT      | PENDING     | sql_table.cc:17410 |              57 |              1 |
| TABLE       | performance_schema | metadata_locks | NULL        |       139394296038784 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:7688  |         3518506 |              1 |
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
8 rows in set (0.00 sec)

    Threads 3513381, 3519276, 3519277, 3519278, and 3519279 on the read-only node all hold SHARED_READ locks on test/t1. The multiple threads appear because the parallel query feature is enabled — each parallel worker holds its own MDL.

    Step 3: Query performance_schema.threads on the read-only node to identify the root session:

    /*force_node='pi-bp186ko4o21wl****'*/ SELECT * FROM performance_schema.threads
WHERE THREAD_ID IN (3519278, 3513381, 3519279, 3519276, 3519277)\G

    Sample output:

    *************************** 1. row ***************************
          THREAD_ID: 3513381
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
         PROCESSLIST_ID: 538961413
       PROCESSLIST_USER: zyg_root
       PROCESSLIST_HOST: 172.17.28.253
         PROCESSLIST_DB: test
    PROCESSLIST_COMMAND: Connect
       PROCESSLIST_TIME: 103
      PROCESSLIST_STATE: User sleep
       PROCESSLIST_INFO: select *,sleep(60) from t1
...
*************************** 2. row ***************************
          THREAD_ID: 3519276
               NAME: thread/sql/parallel_worker
               TYPE: FOREGROUND
         PROCESSLIST_ID: 1855915
       PROCESSLIST_USER: zyg_root
       PROCESSLIST_HOST: 172.17.28.253
         PROCESSLIST_DB: test
    PROCESSLIST_COMMAND: Sleep
       PROCESSLIST_TIME: 103
      PROCESSLIST_STATE: Sending data
       PROCESSLIST_INFO: select *,sleep(60) from t1
       PARENT_THREAD_ID: 3513381
...
5 rows in set (0.00 sec)

    Thread 3513381 is running select *,sleep(60) from t1 on the read-only node, with four parallel worker threads spawned from it. Wait for the query to complete, or terminate it with KILL 538961413. Then re-execute the ALTER TABLE statement.

    Contact us

    If you have any questions about DDL operations, please contact technical support.

    What's next

    • Contact us if you have further questions about DDL operations.