How do I use the OPTIMIZE TABLE statement to release the tablespace of an ApsaraDB RDS for MySQL instance? - ApsaraDB RDS

When you execute the DELETE statement to delete data, the DELETE statement only marks the location of the data record or the data page as reusable. The size of disk files does not change, and the tablespace is not directly reclaimed. In this case, you can execute the OPTIMIZE TABLE statement to release the tablespace.

Prerequisites

You can execute the OPTIMIZE TABLE statement only when your RDS instance runs the InnoDB or MyISAM storage engine.
The available storage of your RDS instance must be greater than or equal to the size of the tablespace that you want to release. If the available storage is insufficient, we recommend that you expand the storage capacity of your RDS instance. For more information, see Change the specifications of an ApsaraDB RDS for MySQL instance.
Note When you execute the OPTIMIZE TABLE statement on a table, the table data is copied to a new temporary table. This increases the storage usage of your RDS instance.

Usage notes

If you have not executed the DELETE statement to delete a large amount of table data, the OPTIMIZE TABLE statement cannot reduce the storage occupied by the table.
Note If the available storage of your RDS 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:
- Change the specifications of an ApsaraDB RDS for MySQL instance
- Configure automatic storage expansion
The OPTIMIZE TABLE statement is executed on your RDS instance that runs MySQL 5.7 or MySQL 8.0 by using online DDL, and concurrent DML operations are allowed. 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, your RDS instance may be unavailable and monitoring breakpoints may exist. We recommend that you execute this statement during off-peak hours.

Use the CLI to release a tablespace

Connect to your RDS instance. For more information, see Use a database client or the CLI to connect to an ApsaraDB RDS for MySQL instance.
Execute the following SQL statement to release the tablespace:
```
optimize table [$Database1].[Table1],[$Database2].[Table2]
```
Note
- [$Database1] and [$Database2] are database names. [Table1] and [Table2] are table names.
- If you execute the OPTIMIZE TABLE statement on your RDS instance that runs the InnoDB storage engine, the following information is returned. You can ignore the information. If OK is included in the output, the execution is successful. 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 your RDS instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance.
In the left-side navigation pane, click the ID of your RDS instance, double-click the required database, right-click a table name, and then select Batch operation table.
Select the name of the table whose tablespace you want to release and choose Table Maintenance > Optimize Table.
In the dialog box that appears, confirm the information and click OK.