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 modify tables in the target database. For more information, see View my permissions and Submit a ticket to request 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
When you reclaim fragmented space from a large table, ensure that the remaining storage space of the instance is at least two to three times the size of the table. You should also closely monitor the remaining storage space of the instance during the change process.
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 table fragmentation space size
In the DMS SQL Console, you can enter the following SQL statement to view the fragmented space in a table: SHOW TABLE STATUS LIKE 'table_name';. For more information about how to run queries in the SQL Console, see Introduction to the SQL window.
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.
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.
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 lockless schema changes using lockless change tickets.
After the lockless change feature is enabled for an instance, DMS automatically reclaims fragmented space each time you execute a lockless schema evolution ticket. To ensure that the system reclaims the fragmented space more reliably and to reduce the impact on dependent or associated tables, you can use one of the following SQL statements in the ticket to reorganize and reclaim the space:
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';NoteAlthough all the preceding SQL statements can reclaim space, the effect of reclaiming fragments is typically less thorough when they are run locklessly in DMS, including
OPTIMIZE TABLE, compared with runningOPTIMIZE TABLEnatively.