Reclaim tablespace fragments-Data Management-阿里云帮助中心

If the INSERT, UPDATE, and DELETE statements are frequently executed in a MySQL database, data is no longer continuously stored on the disk, and tablespace fragments are generated. The tablespace fragments degrade the database performance. If you perform common operations such as executing the OPTIMIZE TABLE statement to recycle the tablespace fragments, the table may be locked and the business may be affected. The lock-free change feature of Data Management (DMS) can automatically recycle tablespace fragments while preventing your business from being affected by locked tables during the database change. This improves database performance and efficiency, and reduces storage costs. This topic describes how to submit a lock-free schema change ticket in DMS to automatically recycle tablespace fragments.

Prerequisites

You have permissions to alter tables in the database in which you want to defragment space. For more information, see View your permissions and Manage permissions.
The lock-free schema change feature is enabled for the MySQL database instance. For more information, see Enable lock-free schema change.

Usage notes

When reclaiming tablespace fragments from a large table, ensure that the available storage space on the instance is at least two to three times the size of the target table. Also, closely monitor the available storage space on the instance during the operation.

Important

When you recycle the tablespace fragments in a large table, you may need a copy to temporarily store data, which occupies extra storage. If the remaining storage space of the database instance is insufficient, DMS may fail to recycle the tablespace fragments, or the database instance may be locked.
DMS recycles tablespace fragments by executing DDL statements to change schemas without locking tables. Take note that the success rate of statement execution cannot reach 100%.

View the size of tablespace fragments

You can check the size of tablespace fragments by running the following SQL statement in the SQL Console of DMS: SHOW TABLE STATUS LIKE 'table_name';. For detailed instructions on using the SQL Console, see SQL Console.

In the query results, the Data_free column indicates the amount of fragmented space in the table, measured in bytes.

Procedure

Log in to DMS 5.0.
In the top navigation bar, choose Database Development > Data Change > Lock-free Change.
Note
If you use 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 a lock-free schema change ticket. For more information, see Perform lockless schema changes using lockless change tickets.

After you enable the lock-free schema change feature for an instance, any lock-free schema change ticket you submit will automatically reclaim tablespace fragments. However, for a more stable reclamation and to minimize impact on related tables, we recommend using one of the following SQL statements in your ticket to explicitly reorganize the table:
- 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
  Although these SQL statements reclaim space, a lock-free operation in DMS (including OPTIMIZE TABLE) may not be as thorough as the native OPTIMIZE TABLE command.