What is nonblocking DDL and what features does it have - PolarDB

PolarDB introduces the nonblocking DDL (Nonblock DDL) feature. Nonblock DDL prevents connection accumulation and blocking caused by prolonged failure to acquire MDL locks during DDL execution, effectively resolving DDL-induced table lock issues.

Background

When you execute a DDL statement, if the target table has uncommitted long-running transactions or large queries, the DDL statement keeps waiting to acquire an MDL-X lock. In PolarDB, because MDL-X locks have the highest priority, while the DDL statement waits for the MDL-X lock, all new transactions on the target table are blocked. This can cause connection accumulation and blocking, which may lead to a complete business system crash. PolarDB provides the Nonblock DDL feature, which ensures that new transactions can still access the target table even when the MDL-X lock cannot be acquired, thereby maintaining business system stability.

Applicable scope

PolarDB for MySQL clusters must meet one of the following conditions:

PolarDB for MySQL8.0.1 with a revision version of 8.0.1.1.29 or later.
PolarDB for MySQL8.0.2 with a revision version of 8.0.2.2.12 or later.

You can check the engine version to confirm your cluster version.

Precautions

Enabling the Nonblock DDL feature lowers the priority of DDL statements. This also increases the probability of DDL execution failures due to MDL lock acquisition failures.

Limits

PolarDB for MySQL8.0.1 with a revision version of 8.0.1.1.29 or later, and version 8.0.2.2.12:
- Only the ALTER TABLE statement supports Nonblock DDL. It is most effective for ALTER TABLE table_name ADD INDEX index_name ( `column1`, `column2`, `column3` ) statements.
- For tables created on the InnoDB engine, use the ALTER TABLE table_name engine=innodb command instead of the OPTIMIZE TABLE table_name command for defragmentation.
PolarDB for MySQL8.0.2 with a revision version of 8.0.2.2.13 or later:

ALTER TABLE, OPTIMIZE TABLE and TRUNCATE TABLE statements support Nonblock DDL.

Usage

You can first enable Nonblock DDL by using the loose_polar_nonblock_ddl_mode parameter, and configure the loose_polar_nonblock_ddl_retry_times parameter to set the number of MDL-X lock timeout retries, the loose_polar_nonblock_ddl_retry_interval parameter to set the retry interval for MDL-X lock timeout, and the loose_polar_nonblock_ddl_lock_wait_timeout parameter to set the MDL-X lock timeout. For more information, see Set cluster parameters and node parameters. The following table describes the parameters.

Parameter	Level	Description
loose_polar_nonblock_ddl_mode	Session	The Nonblock DDL switch. Valid values: ON: enables Nonblock DDL. OFF (default): disables Nonblock DDL.
loose_polar_nonblock_ddl_retry_times	Session	The number of MDL-X lock timeout retries. Valid values: 0 to 31536000. Default value: 0 (calculated from the `lock_wait_timeout` parameter). Note We recommend that you set this parameter to 4194304.
loose_polar_nonblock_ddl_retry_interval	Session	The retry interval for MDL-X lock timeout. Valid values: 1 to 31536000. Unit: seconds. Default value: 6.
loose_polar_nonblock_ddl_lock_wait_timeout	Session	The MDL-X lock timeout. Valid values: 1 to 31536000. Unit: seconds. Default value: 1.

Performance comparison

This topic compares the impact of enabling and disabling the Nonblock DDL feature on business operations, and also compares performance with the gh-ost external tool for schema changes.

Test tool

SysBench is a cross-platform, multi-threaded, modular benchmarking tool used to evaluate the performance of core parameters when the system runs high-load databases. SysBench is used to bypass complex database benchmark settings and quickly understand the performance of the database system even without a database installed. For more information about how to use SysBench, see Performance test method (OLTP).

Test environment

A standard editionPolarDB for MySQL 8.0 cluster with 8 cores and 64 GB of memory.

Test method

Use SysBench to create a test table sbtest1 and insert 1,000,000 rows of data.

./oltp_read_write.lua --mysql-host="Cluster endpoint" --mysql-port="Port number" --mysql-user="Username" --mysql-password="Password" --mysql-db="sbtest" --tables=1 --table-size=1000000 --report-interval=1 --percentile=99 --threads=8 --time=6000 prepare

Use oltp_read_write.lua in SysBench to simulate business workloads.

./oltp_read_write.lua --mysql-host="Cluster endpoint" --mysql-port="Port number" --mysql-user="Username" --mysql-password="Password" --mysql-db="sbtest" --tables=1 --table-size=1000000 --report-interval=1 --percentile=99 --threads=8 --time=6000 run

Start a transaction on the target table sbtest1 without committing it. This transaction holds the MDL lock on the target table sbtest1.
```
/* session 1 */
begin;
select * from sbtest1;
```
In a new session, add a column to the table sbtest1 with and without Nonblock DDL enabled, and observe TPS changes.
```
/* session 2 */
alter table sbtest1 add column d int;
```

Use the gh-ost tool to add a column and observe TPS changes (Enable binary logging).

./gh-ost --assume-rbr --user="Username" --password="Password" --host="Cluster endpoint" --port="Port number" --database="sbtest" --table="sbtest1"  --alter="ADD COLUMN d INT" --allow-on-master --aliyun-rds --initially-drop-old-table --initially-drop-ghost-table --execute;

Test results

With Nonblock DDL disabled, TPS continuously drops to zero. The default timeout is 31,536,000 seconds, which severely affects business operations.

[ 6s ] thds: 8 tps: 288.00 qps: 5751.00 (r/w/o: 4023.00/1152.00/576.00)
[ 7s ] thds: 8 tps: 288.00 qps: 5742.90 (r/w/o: 4016.93/1151.98/573.99)
[ 8s ] thds: 8 tps: 272.00 qps: 5462.06 (r/w/o: 3827.04/1089.01/546.01)
[ 9s ] thds: 8 tps: 290.00 qps: 5782.06 (r/w/o: 4043.04/1159.01/580.01)
[ 10s ] thds: 8 tps: 270.00 qps: 5436.99 (r/w/o: 3809.00/1088.00/540.00)
[ 11s ] thds: 8 tps: 289.00 qps: 5746.91 (r/w/o: 4020.94/1147.98/577.99)
[ 12s ] thds: 8 tps: 277.00 qps: 5558.08 (r/w/o: 3893.05/1111.02/554.01)
[ 13s ] thds: 8 tps: 284.00 qps: 5672.96 (r/w/o: 3967.97/1136.99/568.00)
[ 14s ] thds: 8 tps: 238.00 qps: 4767.03 (r/w/o: 3341.02/950.01/476.00)
[ 15s ] thds: 8 tps: 321.00 qps: 6429.97 (r/w/o: 4502.98/1284.99/642.00)
[ 16s ] thds: 8 tps: 278.00 qps: 5529.98 (r/w/o: 3865.98/1109.00/555.00)
[ 17s ] thds: 8 tps: 284.00 qps: 5698.06 (r/w/o: 3992.04/1137.01/569.01)
[ 18s ] thds: 8 tps: 123.00 qps: 2474.00 (r/w/o: 1730.00/491.00/253.00)
[ 19s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 20s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 21s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 22s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 23s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 24s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 25s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 26s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 27s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 28s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 29s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 30s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 31s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 32s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 33s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 34s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 35s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 36s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 37s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 38s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 39s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00
[ 40s ] thds: 8 tps:   0.00 qps:  0.00 (r/w/o: 0.00/0.00/0.00) lat (ms, 99%): 0.00

With Nonblock DDL enabled, TPS periodically dips but never drops to zero. The impact on business operations is minimal, which ensures business system stability. In the SysBench output, TPS periodically dips from the normal ~280 to ~70 approximately every 7 seconds (at 12s, 19s, 26s, 33s), and then spikes to ~483-489 in the next second before returning to normal.

[ 9s ] thds: 8 tps: 280.00 qps: 5600.00 (r/w/o: 3924.00/1116.00/560.00) la
[ 10s ] thds: 8 tps: 278.99 qps: 5574.83 (r/w/o: 3903.88/1112.97/557.98) la
[ 11s ] thds: 8 tps: 276.00 qps: 5517.98 (r/w/o: 3859.99/1106.00/552.00)
[ 12s ] thds: 8 tps: 71.00 qps: 1404.04 (r/w/o: 975.03/282.01/147.00) lat
[ 13s ] thds: 8 tps: 485.99 qps: 9817.74 (r/w/o: 6900.82/1944.95/971.97)
[ 14s ] thds: 8 tps: 290.01 qps: 5773.20 (r/w/o: 4034.14/1159.04/580.02)
[ 15s ] thds: 8 tps: 278.00 qps: 5572.01 (r/w/o: 3904.01/1112.00/556.00)
[ 16s ] thds: 8 tps: 270.00 qps: 5404.96 (r/w/o: 3785.98/1079.99/539.00)
[ 17s ] thds: 8 tps: 283.00 qps: 5633.02 (r/w/o: 3933.02/1134.00/566.00)
[ 18s ] thds: 8 tps: 286.00 qps: 5734.99 (r/w/o: 4020.99/1142.00/572.00)
[ 19s ] thds: 8 tps: 66.00 qps: 1334.00 (r/w/o: 931.00/264.00/139.00) lat
[ 20s ] thds: 8 tps: 489.99 qps: 9900.72 (r/w/o: 6956.80/1963.94/979.97)
[ 21s ] thds: 8 tps: 283.01 qps: 5633.14 (r/w/o: 3938.10/1129.03/566.01)
[ 22s ] thds: 8 tps: 271.00 qps: 5438.02 (r/w/o: 3811.01/1085.00/542.00)
[ 23s ] thds: 8 tps: 286.99 qps: 5744.85 (r/w/o: 4023.89/1146.97/573.98)
[ 24s ] thds: 8 tps: 273.01 qps: 5456.14 (r/w/o: 3819.10/1091.03/546.01)
[ 25s ] thds: 8 tps: 281.99 qps: 5626.83 (r/w/o: 3933.88/1128.96/563.98)
[ 26s ] thds: 8 tps: 77.00 qps: 1580.05 (r/w/o: 1111.03/307.01/162.00) lat
[ 27s ] thds: 8 tps: 483.00 qps: 9731.03 (r/w/o: 6832.02/1933.01/966.00)
[ 28s ] thds: 8 tps: 282.00 qps: 5631.01 (r/w/o: 3941.01/1127.00/563.00)
[ 29s ] thds: 8 tps: 278.00 qps: 5590.00 (r/w/o: 3919.00/1114.00/557.00)
[ 30s ] thds: 8 tps: 272.00 qps: 5436.99 (r/w/o: 3806.99/1086.00/544.00)
[ 31s ] thds: 8 tps: 292.00 qps: 5836.99 (r/w/o: 4084.99/1168.00/584.00)
[ 32s ] thds: 8 tps: 278.99 qps: 5579.81 (r/w/o: 3903.87/1117.96/557.98)
[ 33s ] thds: 8 tps: 70.00 qps: 1434.04 (r/w/o: 1008.03/278.01/148.00) lat
[ 34s ] thds: 8 tps: 483.00 qps: 9758.07 (r/w/o: 6850.05/1942.01/966.01)
[ 35s ] thds: 8 tps: 281.00 qps: 5606.01 (r/w/o: 3927.01/1117.00/562.00)
[ 36s ] thds: 8 tps: 282.00 qps: 5642.98 (r/w/o: 3953.99/1125.00/564.00)

When gh-ost is used for online schema changes, TPS periodically drops to zero. The impact on business operations is significant. This is caused by brief table locks during the cut-over phase.

[ 18s ] thds: 4 tps: 257.00 qps: 5141.99 (r/w/o: 3594.99/1034.00/513.
[ 19s ] thds: 4 tps: 235.00 qps: 4696.99 (r/w/o: 3289.99/936.00/471.0
[ 20s ] thds: 4 tps: 259.00 qps: 5199.06 (r/w/o: 3643.04/1038.01/518.
[ 21s ] thds: 4 tps: 262.00 qps: 5224.98 (r/w/o: 3656.99/1044.00/524.
[ 22s ] thds: 4 tps: 277.00 qps: 5533.95 (r/w/o: 3868.96/1110.99/553.
[ 23s ] thds: 4 tps: 233.00 qps: 4682.03 (r/w/o: 3281.02/935.01/466.0
[ 24s ] thds: 4 tps: 272.00 qps: 5419.97 (r/w/o: 3789.98/1085.99/544.
[ 25s ] thds: 4 tps: 254.00 qps: 5092.04 (r/w/o: 3572.03/1012.01/508.
[ 26s ] thds: 4 tps: 252.00 qps: 5038.94 (r/w/o: 3525.96/1008.99/503.
[ 27s ] thds: 4 tps: 264.00 qps: 5265.06 (r/w/o: 3682.05/1055.01/528.
[ 28s ] thds: 4 tps: 262.00 qps: 5239.95 (r/w/o: 3667.96/1047.99/523.
[ 29s ] thds: 4 tps: 254.00 qps: 5095.06 (r/w/o: 3567.04/1020.01/508.
[ 30s ] thds: 4 tps: 26.00 qps: 529.00 (r/w/o: 373.00/100.00/56.00) l
[ 31s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 32s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 33s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 34s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 35s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 36s ] thds: 4 tps: 254.00 qps: 5124.96 (r/w/o: 3596.97/1019.99/508.
[ 37s ] thds: 4 tps: 38.00 qps: 775.00 (r/w/o: 547.00/148.00/80.00) l
[ 38s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 39s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 40s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 41s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 42s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 43s ] thds: 4 tps: 239.00 qps: 4837.04 (r/w/o: 3396.03/963.01/478.0
[ 44s ] thds: 4 tps: 43.00 qps: 863.00 (r/w/o: 608.00/165.00/90.00) l
[ 45s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 46s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 47s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 48s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 49s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 50s ] thds: 4 tps: 484.00 qps: 9731.07 (r/w/o: 6823.05/1940.01/968.
[ 51s ] thds: 4 tps: 57.00 qps: 1134.00 (r/w/o: 793.00/224.00/117.00)
[ 52s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 53s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 54s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 55s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 56s ] thds: 4 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,9
[ 57s ] thds: 4 tps: 476.00 qps: 9547.93 (r/w/o: 6691.95/1903.99/951.
[ 58s ] thds: 4 tps: 62.00 qps: 1271.99 (r/w/o: 896.00/248.00/128.00)

Performance comparison between Nonblock DDL and gh-ost

The following is a performance comparison of adding columns to a table with 1 million rows by using Nonblock DDL with traditional methods (INSTANT, INPLACE, COPY) and the gh-ost tool:

Under no workload, the following figure shows that Nonblock DDL with traditional methods (INSTANT, INPLACE, COPY) completes faster than gh-ost.
With the SysBench oltp_read_write workload simulated, the following figure shows that Nonblock DDL with traditional methods (INSTANT, INPLACE, COPY) completes faster than gh-ost.

Test conclusion

The Nonblock DDL feature can better prevent new transaction blocking and TPS drops to zero during DDL execution, thereby ensuring business system stability as much as possible. It also provides more efficient DDL change capabilities.

Contact us

If you have questions about DDL operations, Contact us.