All Products
Search
Document Center

Defragment space without locking a table

Last Updated: Apr 15, 2020

In the InnoDB engine for MySQL, frequent update and delete operations tend to generate fragmented space, which cannot be reclaimed before being defragmented.
When the fragmentation rate reaches a certain threshold, you need to run the OPTIMIZE TABLE statement to improve database performance and free up storage space. However, running this statement will lock the table. With the continuous increase of table data, the length of time that the table is locked increases.
This seriously affects the business continuity. It is better to reclaim the tablespace without locking the table. To achieve this goal, DMS Enterprise provides the function of changing the schema of a table without locking the table.

Procedure

  1. Make sure that the target database instance where the schema of a table needs to be changed has been registered with DMS Enterprise on the System Management > Instance Management page. For more information, see Instance management.
  2. Make sure that the following option is selected for the parameter Schema Change without Table Locking for the target database instance where the schema of a table needs to be changed: Enable (Modify schemas without locking tables first)

  3. Change the schema of a table without locking the table to reclaim the tablespace.

    • 3.1 Common users who have permissions to change the schema of a table in the target database can submit a data change ticket to commit the following statement: alter table table_name comment 'comment on the table after the change';
    • 3.2 The DBA or administrator can choose System Management > Task Management to create an SQL task, and then commit the following statement: alter table table_name comment 'comment on the table after the change';
  4. After a common change is completed, the tablespace has been defragmented and the table is not locked during the change. Therefore, the business continuity is not affected at all.

Considerations

  1. If you do not need to change the schema of a table in the near future, you can enter the comment on either the table or a certain field in the preceding ALTER statement.
  2. If you need to change the schema of a table and the option Enable (Modify schemas without locking tables first) is selected for the corresponding instance in the DMS Enterprise console, the tablespace is defragmented along with the change. That is, no more change operation is required for defragmenting the tablespace.