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

When you use the DELETE statement to delete data, the database only marks the records or data pages as reusable and does not directly reduce the size of the disk file. This means that the tablespace is not automatically reclaimed. To release the tablespace, you can execute the OPTIMIZE TABLE statement.

Prerequisites

Only the InnoDB and MyISAM engines support the OPTIMIZE TABLE statement.
The available storage of your instance must be greater than or equal to the size of the tablespace that you want to release. If the available storage of your instance is insufficient, it is recommended to expand the storage capacity first. For more information, see change configuration.

Note
When you execute the OPTIMIZE TABLE statement, the table data is copied to a newly created temporary table, which increases the disk usage of the instance.

Considerations

If you do not use the DELETE statement to delete a large amount of data, directly executing the OPTIMIZE TABLE statement will not effectively reduce the tablespace usage. Therefore, you need to first delete data using the DELETE statement and then execute OPTIMIZE TABLE to release the tablespace.
Note
If the available storage of your instance is insufficient and you do not perform operations to delete a large amount of table data, you can expand the storage capacity based on the following topics:
- Manually change configuration
- Set automatic storage expansion
In RDS MySQL 5.7 and 8.0, the OPTIMIZE TABLE is executed using the online DDL method, which supports concurrent DML operations. However, executing this operation on large tables may cause sudden IO and buffer resource usage, posing a threat of table locking or resource contention. During peak business hours, this may lead to the instance being inactive or monitoring interruptions. Therefore, it is recommended to perform this operation during off-peak business hours to avoid affecting normal business operations.

Use the command line

Connect to the MySQL database. For more information, see connect to an RDS MySQL instance through the command line or client.
Execute the following SQL statement to release the tablespace.
```
OPTIMIZE TABLE [$Database1].[Table1],[$Database2].[Table2]
```
Note
- [$Database1] and [$Database2] are database names, and [Table1] and [Table2] are table names.
- When you execute the OPTIMIZE TABLE statement in the InnoDB engine, the following prompt message will appear. This message is the result of normal execution, and you can ignore it as long as it returns "ok". For more information, see OPTIMIZE TABLE Statement.
  
  Table does not support optimize, doing recreate + analyze instead

Use DMS

Log on to the MySQL database. For more information, see log on to an RDS database through DMS.
In the left pane, select the instance ID of the destination instance, then double-click the destination database, right-click any table name, and then select Batch Table Operations .
Select the table names 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 .