Recycle tablespace fragments in MySQL by using lock-free schema change - Data Management

Frequent INSERT, UPDATE, and DELETE operations in MySQL cause data to become non-contiguous on disk, leaving behind tablespace fragments that degrade database performance. Running OPTIMIZE TABLE natively reclaims this space but locks the table, blocking concurrent reads and writes.

The lock-free change feature in Data Management (DMS) reclaims tablespace fragments using DDL statements that do not lock the table, keeping your application available throughout the operation. This improves database performance and efficiency, and reduces storage costs.

Prerequisites

Before you begin, ensure that you have:

Table modification permissions on the target database. To check your current permissions, see View my permissions. If you need additional permissions, see Submit a ticket to request permissions
The lock-free schema change feature enabled for the MySQL database instance. See Enable the lock-free schema change feature

Check the fragmentation size

Before reclaiming space, check how much fragmented space the table has accumulated. Run the following statement in the DMS SQL Console:

SHOW TABLE STATUS LIKE 'table_name';

The Data_free column in the result shows the fragmented space size in bytes. For more information about running queries in the SQL Console, see Introduction to the SQL window.

Usage notes

Lock-free change requires a temporary copy to store data during the operation. This means the operation requires additional storage for the copy.

Before reclaiming space from a large table, take the following steps:

Check available storage: Make sure the database instance has at least two to three times the table size in free storage. If storage runs out mid-operation, DMS may fail to complete the reclamation or the instance may be locked.
Monitor storage during the operation: Watch the remaining free storage continuously while the change runs.
Note on success rate: The success rate of lock-free DDL execution cannot reach 100%.

Reclaim fragmented table space

Log on to the DMS console V5.0.
In the top navigation bar, choose Database Development > Data Change > Lock-free Change.
Note
If you are using the DMS console in simple mode, click the icon in the upper-left corner and choose All functions > Database Development > Data Change > Lock-free Change.
Configure and submit a lock-free schema change ticket. See Perform lockless schema changes using lockless change tickets. After you enable lock-free change for an instance, DMS automatically reclaims fragmented space each time a lock-free schema change ticket runs. To make the reclamation more thorough and reduce the impact on dependent tables, include one of the following SQL statements in the ticket:
- OPTIMIZE TABLE table_name;
- ALTER TABLE table_name COMMENT 'The new comment for the table';
- ALTER TABLE table_name ENGINE=InnoDB;
- ALTER TABLE table_name MODIFY COLUMN field_name INT COMMENT 'The new comment for the field';
Note
Running any of these statements through lock-free change in DMS is typically less thorough than running OPTIMIZE TABLE natively against InnoDB. This is a trade-off for keeping the table available during the operation.