All Products
Search
Document Center

Data Management:Recycle tablespace fragments

Last Updated:Dec 11, 2023

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

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.

Note

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.

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.

image.png

Procedure

  1. Log on to the DMS console V5.0.
  2. 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 2022-10-21_15-25-22..png icon in the upper-left corner and choose All functions > Database Development > Data Change > Lock-free Change.

  3. 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';