ApsaraDB RDS:Use the OPTIMIZE TABLE command to release tablespace for an ApsaraDB RDS for MySQL instance

Prerequisites

Before you begin, ensure that you have:

• A table that uses the InnoDB or MyISAM engine. Only these two engines support the OPTIMIZE TABLE statement.

• Remaining disk space on the instance that is greater than or equal to the size of the table you want to optimize.

If the remaining disk space is insufficient, first scale out the disk space. After the operation completes, scale in the disk space as needed. The system refunds the price difference.

Considerations

• Delete data first. OPTIMIZE TABLE does not effectively reduce tablespace usage unless you first delete a large amount of data using the DELETE statement.

• Disk usage increases temporarily. MySQL creates a temporary table to store the reorganized data during the operation. After the operation completes, the temporary table is deleted and disk usage returns to normal.

• Table and index statistics may not update immediately. The disk space is released, but MySQL table statistics are not refreshed promptly. For details, see Why does disk space appear unchanged after OPTIMIZE TABLE?

• Run during off-peak hours. In ApsaraDB RDS for MySQL 5.7 and 8.0, OPTIMIZE TABLE uses the online DDL method, which supports concurrent DML operations. However, running this command on a large table can cause I/O and buffer resource usage to burst, leading to table locking or resource contention. During peak hours, the instance may become unavailable or monitoring may be interrupted.

Run OPTIMIZE TABLE from the command line

1. Connect to the ApsaraDB RDS for MySQL instance from a client.

2. Delete unwanted data using the DELETE statement.

3. Run OPTIMIZE TABLE to reclaim tablespace.

   OPTIMIZE TABLE <database_1>.<table_1>, <database_2>.<table_2>;

   Replace <database_1> and <database_2> with your database names, and <table_1> and <table_2> with your table names. Expected output for InnoDB tables When you run OPTIMIZE TABLE on an InnoDB table, the following output appears. This is normal and indicates the statement ran successfully — you can ignore this message. Verify that the Status column shows OK. For more information, see OPTIMIZE TABLE statement.

   +----------------+----------+----------+-------------------------------------------------------------------+
   | Table          | Op       | Msg_type | Msg_text                                                          |
   +----------------+----------+----------+-------------------------------------------------------------------+
   | mydb.mytable   | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
   | mydb.mytable   | optimize | status   | OK                                                                |
   +----------------+----------+----------+-------------------------------------------------------------------+

Run OPTIMIZE TABLE using DMS

1. Log on to the ApsaraDB RDS for MySQL instance using DMS.

2. In the navigation pane on the left, find and click the target instance. Double-click the destination database. Right-click a table and select Batch Operations.

3. Select the tables for which you want to release space, then choose Table Maintenance > Optimize Table.

   

4. In the dialog box, confirm the information and click Confirm.

FAQ

ALTER TABLE table_name ENGINE=InnoDB;

References

Reclaim space from table fragments