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. This topic describes how to automatically recycle tablespace fragments by submitting a lock-free schema change ticket in Data Management (DMS). You can use the lock-free schema change feature to prevent your business from being affected by locked tables during the database change. This improves database performance and efficiency, and reduces storage costs.
Prerequisites
You have permissions to alter tables in the database in which you want to defragment space. For more information, see View owned permissions and Manage permissions.
The lock-free schema change feature is enabled for the MySQL database instance. For more information, see Enable the lock-free schema change feature.
Usage notes
Before you recycle the tablespace fragments in a large table, make sure that the remaining storage space of the database instance is at least twice the size of the table. Pay close attention to the remaining storage space of the database instance during the database change.
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
Log on to the MySQL database instance in the DMS console. On the SQLConsole tab, you can execute the SHOW TABLE STATUS LIKE 'table_name'; statement to view the size of tablespace fragments. For more information about how to query data on the SQLConsole tab, see Overview.
The following figure shows an example of the query result. The Data_free parameter indicates the size of the tablespace fragments in the table. Unit: bytes.
Procedure
- Log on to the DMS console V5.0.
In the top navigation bar, choose Database Development > Data Change > Lock-free Change.
NoteIf you use the DMS console in simple mode, click the
icon in the upper-left corner and choose . Configure a lock-free schema change ticket. For more information, see Perform lock-free DDL operations.
After the lock-free schema change feature is enabled for a database instance, DMS automatically recycles tablespace fragments each time you submit a lock-free schema change ticket. To ensure that DMS can recycle the tablespace fragments in a stable manner and reduce the impact on other tables that depend on or are associated with the table, we recommend that you use the following SQL statements in a ticket to defragment spaces and recycle the tablespace fragments:
ALTER TABLE table_name COMMENT 'Comment of a modified table';ALTER TABLE table_name ENGINE=InnoDB;OPTIMIZE TABLE table_name;ALTER TABLE table_name MODIFY COLUMN field_name INT COMMENT 'Comment of a modified field';