PolarDB:Online partition maintenance

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for MySQL 8.0 cluster at revision version 8.0.2.2.0 or later. To check your version, see Query the engine version.

• The partition_level_mdl_enabled parameter set to ON. For instructions, see Specify cluster and node parameters.

• The transaction_isolation parameter set to READ-COMMITTED at the global level. For instructions, see Specify cluster and node parameters.

Supported operations

Online partition maintenance applies to the following DDL operations. The ADD PARTITION operation is supported for RANGE and LIST partitioned tables. Support for additional DDL operations and partition types will be available in a future release.

Limitations

If the isolation level is set to REPEATABLE-READ or higher and DDL operations run concurrently, the following error may appear:

ERROR HY000: Table definition has changed, please retry transaction

This is expected behavior. The error occurs because a DML statement accessed a partition that a concurrent DDL operation just created. Retry the transaction to resolve it.

Usage example

The following example uses two concurrent clients to demonstrate how online partition maintenance lets DML and DDL operations coexist.

-- Client 1: View the current table structure
SHOW CREATE TABLE tr\G
*************************** 1. row ***************************
       Table: tr
Create Table: CREATE TABLE `tr` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`purchased`))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */
1 row in set (0.00 sec)

-- Client 1: Start a transaction and query data
BEGIN;
Query OK, 0 rows affected (0.01 sec)

SELECT * FROM tr WHERE purchased >= '2010-01-01';
+------+----------------+------------+
| id   | name           | purchased  |
+------+----------------+------------+
|    5 | exercise bike  | 2014-05-09 |
|    7 | espresso maker | 2011-11-22 |
+------+----------------+------------+
2 rows in set (0.01 sec)

-- Client 2: While client 1's transaction is open, add a new partition and insert data
ALTER TABLE tr ADD PARTITION (PARTITION p6 VALUES LESS THAN (2020));
INSERT INTO tr VALUES (11, 'hope', '2017-11-04'), (12, 'carmen', '2018-06-08');

-- Client 1: Query again in the same transaction — the new partition's data is visible
SELECT * FROM tr WHERE purchased >= '2010-01-01';
+------+----------------+------------+
| id   | name           | purchased  |
+------+----------------+------------+
|    5 | exercise bike  | 2014-05-09 |
|    7 | espresso maker | 2011-11-22 |
|   11 | hope           | 2017-11-04 |
|   12 | carmen         | 2018-06-08 |
+------+----------------+------------+
4 rows in set (0.00 sec)

-- Client 2: Drop an old partition while client 1's transaction is still open
ALTER TABLE tr DROP PARTITION p0;

-- Client 1: Confirm the table structure reflects both changes — p6 added, p0 dropped
SHOW CREATE TABLE tr\G
*************************** 1. row ***************************
       Table: tr
Create Table: CREATE TABLE `tr` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`purchased`))
(PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (2020) ENGINE = InnoDB) */
1 row in set (0.00 sec)

-- Client 1: Commit the transaction
COMMIT;

Performance comparison

The following two scenarios compare DML throughput with and without online partition maintenance.

Scenario 1: DDL blocked by a long-running transaction

In standard MySQL, when a DDL operation cannot proceed because an open transaction holds a lock, the DDL operation blocks all subsequent DML operations — causing throughput to drop to zero until the DDL is cancelled or the transaction commits.

With online partition maintenance enabled:

• Normal throughput is identical to when the feature is disabled — enabling it adds no overhead.

• Long-running transactions no longer block partition DDL operations. DML traffic remains stable throughout.

Scenario 2: Time-consuming DDL operations

When a DDL operation is slow (for example, rebuilding a large partition), it can cause DML throughput to jitter severely even when no blocking transaction is involved.

With online partition maintenance enabled, time-consuming DDL operations have little impact on DML throughput.

View MDL status

When a DDL operation is running, query performance_schema.metadata_locks to see the partition-level lock state.

The following example shows the lock table when client 1 holds a read lock on partition p5, and client 2 then attempts to drop that partition.

-- Client 1: Start a transaction and query partition p5
BEGIN;
SELECT * FROM tr WHERE purchased >= '2010-01-01';
+------+----------------+------------+
| id   | name           | purchased  |
+------+----------------+------------+
|    5 | exercise bike  | 2014-05-09 |
|    7 | espresso maker | 2011-11-22 |
+------+----------------+------------+
2 rows in set (0.01 sec)

-- Client 1: Check the lock table
SELECT * FROM performance_schema.metadata_locks;
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| 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               | tr             | NULL        |       140734887898944 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6759 |              67 |             17 |
| PARTITION   | test               | tr             | p5          |       140734887896704 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6502 |              67 |             17 |
| TABLE       | performance_schema | metadata_locks | NULL        |       140734879511488 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6759 |              68 |              4 |
| SCHEMA      | performance_schema | NULL           | NULL        |       140734879511648 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | dd_schema.cc:108  |              68 |              4 |
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
4 rows in set (0.02 sec)

The output shows two locks held by thread 67 (client 1): a SHARED_READ lock at the table level on tr, and a SHARED_READ lock at the partition level on p5 (after partition pruning). The OWNER_THREAD_ID column identifies which thread holds the lock.

-- Client 2: Attempt to drop partition p5 — this enters a waiting state
--   because client 1 still holds a SHARED_READ lock on p5
ALTER TABLE tr DROP PARTITION p5;

-- Confirm the DDL is waiting for the partition-level MDL
SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+------+-------------------------------------+----------------------------------+
| Id | User            | Host      | db   | Command | Time | State                               | Info                             |
+----+-----------------+-----------+------+---------+------+-------------------------------------+----------------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  | 1550 | Waiting on empty queue              | NULL                             |
|  8 | root            | localhost | test | Sleep   |  426 |                                     | NULL                             |
|  9 | root            | localhost | NULL | Query   |    0 | starting                            | SHOW PROCESSLIST                 |
| 10 | root            | localhost | test | Query   |   10 | Waiting for partition metadata lock | ALTER TABLE tr DROP PARTITION p5 |
+----+-----------------+-----------+------+---------+------+-------------------------------------+----------------------------------+
4 rows in set (0.00 sec)

The State column shows Waiting for partition metadata lock when a DDL is queued behind a partition-level lock. To unblock the DDL, commit or roll back the transaction on the blocking session. Identify the blocking session using the OWNER_THREAD_ID from the metadata_locks query, then commit or roll back that session's transaction.

-- The DROP PARTITION on client 2 proceeds automatically after client 1 commits

Track online partition maintenance operations

Use the Online_altered_partition status variable to see how many online partition maintenance operations have run.

SHOW STATUS LIKE 'Online_altered_partition';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Online_altered_partition | 2565  |
+--------------------------+-------+
1 row in set (0.00 sec)

Operation video

Demo — PolarDB for MySQL partition-level metadata locking (MDL)