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.
- You can execute the
OPTIMIZE TABLEstatement 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 TABLEstatement on a table, the table data is copied to a new temporary table. This increases the storage usage of your RDS instance.
- If you have not executed the
DELETEstatement to delete a large amount of table data, the
OPTIMIZE TABLEstatement 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:
OPTIMIZE TABLEstatement 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 TABLEstatement 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 .
- In the dialog box that appears, confirm the information and click OK.