How to use the preemptive DDL feature and what are the limits - PolarDB

PolarDB for MySQL supports the preemptive DDL feature. The preemptive DDL feature resolves the following issue: DDL operations fail because large queries and long-running transactions are being performed on the tables of read-only nodes.

Prerequisites

Your PolarDB for MySQL cluster meets one of the following version requirements:

A PolarDB for MySQL 8.0.1 cluster whose revision version is 8.0.1.1.39 or later.
A PolarDB for MySQL 8.0.2 cluster whose revision version is 8.0.2.2.14 or later

For information about how to view the version of a cluster, see the Query the engine version section of the "Engine versions" topic.

Limits

The preemptible DDL feature is supported only for read-only nodes.

Usage notes

If you enable the preemptible DDL feature, the connection to the current table on the read-only node may be interrupted or the SQL statements that have not been executed on the current table may be rolled back. Proceed with caution.
The preemptible DDL feature takes effect only when the value of the loose_replica_lock_wait_timeout parameter is greater than the sum of the value of the loose_polar_mdl_sync_preempt_after_wait_second parameter plus 5.

Background information

PolarDB for MySQL uses a shared storage architecture. When you perform a DDL operation, PolarDB for MySQL first acquires an MDL-X lock on the primary node and then notifies read-only nodes to acquire the MDL-X lock. If transactions accessing tables are being executed on a read-only node at that time, the MDL-X lock synchronization thread is blocked. If the read-only node cannot acquire the MDL-X lock within the timeout period, the client returns the following error code and error message: ERROR 8007 (HY000): Fail to get MDL on replica during DDL synchronize. The following issue usually occurs in a PolarDB for MySQL cluster that contains multiple read-only nodes: DDL operations fail to be performed due to the failure of MDL lock synchronization. To resolve this issue, PolarDB for MySQL provides the preemptible DDL feature.

Use the preemptive DDL feature

You can configure the loose_polar_support_mdl_sync_preemption parameter to enable the preemptible DDL feature and then configure the loose_polar_mdl_sync_preempt_after_wait_second parameter to specify the timeout period during preemption. For more information, see Specify cluster and node parameters. The following table describes the parameters.

Parameter

Level

Description

loose_polar_support_mdl_sync_preemption

Session

Specifies whether to enable the preemptible DDL feature. Valid values:

ON
OFF (default)

loose_polar_mdl_sync_preempt_after_wait_second

Global

The allowed timeout period that elapses when the MDL lock synchronization thread is blocked. If the MDL lock is not synchronized when the specified timeout period is reached, a preemption thread is initiated.

Valid values: 1 to 31536000. Unit: seconds. Default value: 10.

Examples

Disable the preemptible DDL feature

Query the test.t1 table on a read-only node.

mysql> use test;
Database changed
# Execute a large query for 100 seconds.
mysql> select sleep(100) from t1;

Add columns on the primary node.
```
mysql > alter table t1 add column c int;
ERROR 8007 (HY000): Fail to get MDL on replica during DDL synchronize
```
The preceding example shows that when the preemptible DDL feature is disabled, MDL lock synchronization fails and the DDL operation performed on the read-only node is blocked due to long-running transactions on the read-only node. This results in the failure of the DDL operation.

Enable the preemptible DDL feature

Query the test.t1 table on a read-only node.

mysql> use test;
Database changed
# Execute a large query for 100 seconds.
mysql> select sleep(100) from t1;

Add columns on the primary node.
```
mysql> alter table t1 add column c int;
Query OK, 0 rows affected (11.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
```
The preceding example shows that when the preemptive DDL feature is enabled, MDL lock synchronization is blocked due to long-running transactions on the read-only node. After a period of time, a preemption thread is initiated, and the DDL operation is successful.