This topic describes how to reclaim tablespaces.
Prerequisites
The database engine of the cluster that you want to manage is InnoDB, and tables in the cluster do not contain full-text indexes.
Usage notes
When the DELETE statement is executed to delete data, the position or associated data page of the deleted data is marked as reusable. However, the tablespaces used by the deleted data are not reclaimed and the size of the files in storage remains unchanged. In this case, you can use the OPTIMIZE TABLE statement or Data Management (DMS) to reclaim tablespaces.
After you reclaim tablespaces, check the storage usage changes in the Distributed Storage section on the Basic Information page of the cluster.
If the table that you want to manage has only a few fragments, the OPTIMIZE TABLE statement cannot significantly reduce the size of tablespaces. You can use the
DATA_FREEfield ininformation_schema.tablesto view the ratio of fragments in the table.When the OPTIMIZE TABLE statement is executed, the table data is copied to a new temporary table. This temporarily increases the storage usage of the cluster.
For tables that do not contain full-text indexes, the OPTIMIZE TABLE statement is executed by using online DDL to allow concurrent read and write operations.
When you execute the OPTIMIZE TABLE statement on a large table, I/O and buffer bursts occur. As a result, the table may be locked and resource preemption may occur. If you execute the statement during peak hours, the cluster may be unavailable and monitoring breakpoints may exist. Therefore, we recommend that you execute the statement during off-peak hours.
The following table compares the OPTIMIZE TABLE statement and DMS.
If you require high execution efficiency during off-peak hours, we recommend that you use the OPTIMIZE TABLE statement to reclaim tablespaces in an efficient manner.
If you do not require high execution efficiency and want to minimize the impact of reclaiming tablespaces on the cluster loads, we recommend that you use DMS to reclaim tablespaces.
Reclaiming method | Comparison item | ||
Concurrent read and write operations | Execution speed | Throttling | |
Supported | Fast | Not supported | |
Supported | Slow | Supported | |
For more information about how to reclaim tablespaces, join the DingTalk group (ID: 15375044501) for technical support.
Procedure
Use the OPTIMIZE TABLE statement to reclaim tablespaces
Connect to the cluster. For more information, see Database connection.
Execute the following statement to reclaim tablespaces:
OPTIMIZE TABLE [$Database1].[Table1],[$Database2].[Table2]In the preceding statement, [$Database1] and [$Database2] indicate the database names, and [Table1] and [Table2] indicate the table names.
When the OPTIMIZE TABLE statement is executed on a table that uses InnoDB, the following message is returned. The message is a standard response. You can ignore the message and confirm that ok is returned. For more information about the OPTIMIZE TABLE statement, see OPTIMIZE TABLE Statement.
Table does not support optimize, doing recreate + analyze instead
Use DMS to reclaim tablespaces
In the upper-left corner, select the region in which the cluster is deployed.
Find the cluster and click its ID.
In the upper-right corner of the cluster details page, click Log on to Database.
Select the cluster ID, double-click the database that you want to manage, right-click any table name, and then select Batch operation table.
Select one or more tables for which you want to release space and choose Table Maintenance > Optimize Table.
In the message that appears, confirm the optimization information and then click OK.