Use the optimize table statement to release MySQL tablespace - ApsaraDB RDS

When a MySQL table contains a large amount of data, using the DELETE statement to clean up data does not directly release disk space, it only marks database records or data pages as reusable. To actually reclaim tablespace and reduce disk usage, you can use OPTIMIZE TABLE.

Prerequisites

Only InnoDB and MyISAM engines support the OPTIMIZE TABLE statement.
The remaining disk space of the instance must be greater than or equal to the space of the table to be released.
Note
If the remaining disk space of the instance is insufficient, you must first scale up the disk space. After the operation is complete, you can scale down the disk space as needed, and the system will refund the price difference.

Considerations

You must delete a large amount of data first: If you do not first delete a large amount of data using DELETE, directly executing OPTIMIZE TABLE will not effectively reduce tablespace usage.
Temporary increase in disk space usage: When executing OPTIMIZE TABLE, MySQL creates a temporary table to store the reorganized data, which causes disk space to increase temporarily. After the operation is complete, the temporary table is deleted, and disk space usage returns to normal.
Performance impact and peak period risks: In RDS MySQL 5.7 and 8.0, OPTIMIZE TABLE is executed using Online DDL, which supports concurrent DML operations. However, performing this operation on large tables may cause sudden IO and Buffer resource consumption, with risks of table locking or resource contention. During business peak periods, it may even cause the instance to become unavailable or monitoring to be interrupted. Therefore, it is recommended to perform this operation during business off-peak hours to avoid affecting normal business operations.

Use the CLI to release a tablespace

Connect to the RDS MySQL instance through a client.
Use the DELETE statement to clean up unwanted data as needed.
Execute the OPTIMIZE TABLE command to release tablespace.
```
OPTIMIZE TABLE <$Database1>.<Table1>,<$Database2>.<Table2>;
```
Note
- <$Database1> and <$Database2> are database names, <Table1> and <Table2> are table names.
- When executing the OPTIMIZE TABLE statement on an InnoDB engine, you will see the following message. This is a normal execution result that you can ignore. Just confirm that it returns "ok". For more information, see OPTIMIZE TABLE Statement.
  Table does not support optimize, doing recreate + analyze instead

Use DMS to release a tablespace

Log on to the RDS MySQL instance through DMS.
In the left-side panel, select the instance ID of the target instance, then double-click the target database, right-click any table name, and select Batch Operations.
Select the tables for which you want to release space, and then select Table Maintenance > Optimize Table.
In the dialog box that appears, confirm that the change information is correct, and then click Confirm.

References

Reclaim table fragmentation space

FAQ

How to handle when`DELETE`does not release space inRDS MySQL?

In RDS MySQL, when you use the DELETE statement to delete data, this command only marks the record location or data pages as reusable, but the disk file size does not change, meaning the tablespace is not directly reclaimed. This behavior prevents tablespace from being directly reclaimed, creating instance storage space fragmentation that occupies instance storage space.

The following provides two solutions: native DDL or DMS lock-free schema evolution. Note that before executing either solution, you need to ensure that the instance has sufficient remaining space to avoid instance locking due to full instance space.

Organize space fragmentation through commands: Execute OPTIMIZE TABLE or ALTER TABLE <table_name> ENGINE=InnoDB; and other DDL operations to reorganize table data and index structures, thereby releasing fragmented space.
Important
When using native DDL commands, you need to execute them during business off-peak hours to avoid metadata lock blocking. For more information, see Considerations.
Use DMS lock-free schema evolution: If you want to avoid metadata locks, you can consider using DMS lock-free schema evolution to reclaim table fragmentation space.

`TRUNCATE`or`DROP`does not release space in RDS MySQL, how to handle it?

In RDS MySQL, if you find that disk space is not released after executing TRUNCATE or DROP operations, you can handle it according to the following steps:

Confirm space release logic
After executing TRUNCATE or DROP, confirm whether the space has been released by monitoring the disk usage of the instance. Typically, the proportion of the deleted table's size to the total instance space will be reflected as a decrease in disk usage.
Avoid relying on outdated information
If you check the table size through information_schema.tables or the RDS console (Autonomy Service > Quick Diagnostics > Space Analysis), the tablespace might appear unchanged due to data update delays. Therefore, it is recommended to primarily use disk usage as the basis for judgment.
Impact of asynchronous deletion
If the instance has enabled asynchronous deletion (such as Alibaba Cloud's Purge Large File Asynchronously), the space occupied by table files will not be released immediately, but is gradually cleaned up by background processes. In this case, you need to wait for the asynchronous process to complete before the disk space is finally released.