PolarDB:EXPLAIN DDL

Algorithm efficiency ranking

The three algorithms form an efficiency hierarchy. EXPLAIN DDL reports the algorithm the engine actually selects — the most efficient one the operation supports:

Check execution characteristics

The following examples show how to use EXPLAIN DDL to assess whether a DDL operation affects business availability.

All examples use the same test table:

SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` char(1) DEFAULT NULL,
  `c` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Add a column

EXPLAIN ALTER TABLE t1 ADD COLUMN d INT;

*************************** 1. row ***************************
             Error No: 0
            Algorithm: INSTANT
        Metadata Only: Yes
        Rebuilt table: No
     Parallel Support: Not Need
      Parallel Degree: 1
       Concurrent DML: Yes
Possible blocked MDLs:
            Error Msg:
         Suggest Info:
            Statement: EXPLAIN ALTER TABLE t1 ADD COLUMN d int
1 row in set (0.00 sec)

The INSTANT algorithm modifies only metadata without a table rebuild and allows concurrent DML. This operation completes in seconds with minimal business impact.

Rename a table

EXPLAIN ALTER TABLE t1 rename t1_rn;

*************************** 1. row ***************************
             Error No: 0
            Algorithm: INPLACE
        Metadata Only: Yes
        Rebuilt table: No
     Parallel Support: Not Need
      Parallel Degree: 1
       Concurrent DML: Yes
Possible blocked MDLs:
            Error Msg:
         Suggest Info:
            Statement: EXPLAIN ALTER TABLE t1 rename t1_rn
1 row in set (0.01 sec)

The INPLACE algorithm modifies only metadata without a table rebuild and allows concurrent DML. This operation has minimal business impact.

Modify a column type

EXPLAIN ALTER TABLE t1 modify COLUMN a char(1);

*************************** 1. row ***************************
             Error No: 0
            Algorithm: COPY
        Metadata Only: No
        Rebuilt table: Yes
     Parallel Support: No
      Parallel Degree: 1
       Concurrent DML: No
Possible blocked MDLs:
            Error Msg:
         Suggest Info:
            Statement: EXPLAIN ALTER TABLE t1 modify COLUMN a char(1)
1 row in set (0.01 sec)

The COPY algorithm requires a full table rebuild and blocks concurrent DML. This operation has significant business impact — schedule it during off-peak hours.

Rebuild a table

EXPLAIN ALTER TABLE t1 engine= innodb;

*************************** 1. row ***************************
             Error No: 0
            Algorithm: INPLACE
        Metadata Only: No
        Rebuilt table: Yes
     Parallel Support: Yes But Not Enable
      Parallel Degree: 1
       Concurrent DML: Yes
Possible blocked MDLs:
            Error Msg:
         Suggest Info: 1. This DDL operation could use Parallel DDL to speed up.
            Statement: EXPLAIN ALTER TABLE t1 engine= innodb

The INPLACE algorithm requires a full table rebuild but allows concurrent DML. Although the operation does not block reads or writes, a full table rebuild on a large table consumes significant resources — schedule it during off-peak hours.

The Suggest Info field indicates that parallel DDL can accelerate this operation. See Check parallel DDL support for details.

Check parallel DDL support

PolarDB for MySQL supports parallel DDL to speed up DDL operations. Use the Parallel Support and Parallel Degree fields to determine whether a given operation can benefit from parallel DDL.

• If Parallel Support is Yes, But Not Enabled, the operation supports parallel DDL but the feature is not enabled on the cluster. The Suggest Info field displays: This DDL operation could use Parallel DDL to speed up. To enable parallel DDL, see Parallel DDL.

• If Parallel Support is Yes, the feature is enabled and in use. EXPLAIN DDL also recommends an optimal degree of parallelism based on current cluster workload. The Suggest Info field displays: This DDL operation can be accelerated by increasing the value of 'innodb_polar_parallel_ddl_threads'. The recommended value is 8. Adjust innodb_polar_parallel_ddl_threads as recommended to improve performance.

Example: parallel DDL disabled

Check whether parallel DDL is enabled:

MySQL [test]> SHOW variables LIKE "%parallel_ddl_threads%";
+----------------------------------------------------+-------+
| Variable_name                                      | Value |
+----------------------------------------------------+-------+
| innodb_polar_innovate_default_parallel_ddl_threads | 1     |
| innodb_polar_parallel_ddl_threads                  | 1     |
+----------------------------------------------------+-------+
2 rows in set (0.03 sec)

Parallel DDL is not enabled. EXPLAIN the add secondary index operation:

EXPLAIN ALTER TABLE t1 ADD index k_a(a);

*************************** 1. row ***************************
             Error No: 0
            Algorithm: INPLACE
        Metadata Only: No
        Rebuilt table: No
     Parallel Support: Yes But Not Enable
      Parallel Degree: 1
       Concurrent DML: Yes
Possible blocked MDLs:
            Error Msg:
         Suggest Info: 1. This DDL operation could use Parallel DDL to speed up.
            Statement: EXPLAIN ALTER TABLE t1 ADD index k_a(a)
1 row in set (0.01 sec)

Parallel Support: Yes But Not Enabled means the operation can use parallel DDL, but the feature is not active. Enable parallel DDL to accelerate it.

Example: parallel DDL enabled

Set the degree of parallelism to 2:

MySQL [test]> SET innodb_polar_parallel_ddl_threads = 2;
Query OK, 0 rows affected (0.00 sec)

EXPLAIN the same add secondary index operation:

EXPLAIN ALTER TABLE t1 ADD index k_a(a);

*************************** 1. row ***************************
             Error No: 0
            Algorithm: INPLACE
        Metadata Only: No
        Rebuilt table: No
     Parallel Support: Yes
      Parallel Degree: 2
       Concurrent DML: Yes
Possible blocked MDLs:
            Error Msg:
         Suggest Info: 1. This DDL operation can be accelerated by increasing the value of 'innodb_polar_parallel_ddl_threads'. The recommended value is 8.
            Statement: explain ALTER TABLE t1 ADD index k_a(a)
1 row in set (0.01 sec)

Parallel Support: Yes and Parallel Degree: 2 confirm that the operation is using two parallel threads. Because the current cluster workload is low, Suggest Info recommends increasing the degree of parallelism to 8 for better performance.

Detect MDL blocking

DDL operations on a table can be blocked by uncommitted transactions holding a Metadata Lock (MDL) on the same table. In extreme cases, undetected MDL blocks can cause active connection buildup and cluster instability. Use the Possible blocked MDLs field to identify blocking connections before running the DDL.

When blocking exists, Possible blocked MDLs lists the Process IDs of connections with uncommitted transactions. Terminate them with KILL or KILL QUERY to unblock the DDL.

Example: detecting an MDL block

In connection 1, start a transaction on t1 without committing:

MySQL [test]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> select * from t1;
Empty set (0.00 sec)

In connection 2, run EXPLAIN DDL on t1:

EXPLAIN ALTER TABLE t1 engine= innodb;
*************************** 1. row ***************************
             Error No: 0
            Algorithm: INPLACE
        Metadata Only: No
        Rebuilt table: Yes
     Parallel Support: Yes But Not Enable
      Parallel Degree: 1
       Concurrent DML: Yes
Possible blocked MDLs: 18
            Error Msg:
         Suggest Info: 1. This DDL operation may be blocked by the threads listed under 'Possible blocked MDLs'.
2. This DDL operation could use Parallel DDL to speed up.
            Statement: EXPLAIN ALTER TABLE t1 engine= innodb

Possible blocked MDLs: 18 identifies that connection 18 has an uncommitted transaction that will block this DDL. Run KILL 18 or KILL QUERY 18 to terminate it before proceeding.