All Products
Search

This Product

    ApsaraDB RDS:Use OPTIMIZE TABLE to reclaim MySQL tablespace

    Document Center

    ApsaraDB RDS:Use OPTIMIZE TABLE to reclaim MySQL tablespace

    Last Updated:Jul 10, 2026

    The DELETE statement does not immediately release disk space — MySQL only marks records or data pages as reusable. To reclaim the tablespace and reduce disk usage, run the OPTIMIZE TABLE statement.

    Prerequisites

    • The OPTIMIZE TABLE statement is supported only by the InnoDB and MyISAM storage engines.

    • The available disk space of the instance must be greater than or equal to the size of the table that you want to optimize.

      Note

      If the available disk space is insufficient, first expand the disk space. After the optimization completes, you can scale down as needed, and the system refunds the price difference.

    Usage notes

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

    • Temporary increase in disk space usage: OPTIMIZE TABLE creates a temporary table for data reorganization, which temporarily increases disk usage. The temporary table is removed after the operation completes, and disk usage returns to normal.

    • Table and index statistics may not change after reclamation: Although disk space is released, table statistics may not update immediately. For more information, see Why does the storage usage of my ApsaraDB RDS for MySQL instance remain unchanged after I run OPTIMIZE TABLE?.

    • Performance impact and risks during peak hours: On ApsaraDB RDS for MySQL 5.7 and 8.0, OPTIMIZE TABLE uses online DDL, which allows concurrent DML operations. However, optimizing a large table can spike I/O and buffer usage, causing table locks or resource contention that may lead to instance unavailability or monitoring interruptions during peak hours. Perform this operation during off-peak hours.

    Use the command line

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

    2. Use the DELETE statement to delete unnecessary data based on your business requirements.

    3. Run the OPTIMIZE TABLE statement to reclaim the tablespace.

      OPTIMIZE TABLE <$Database1>.<Table1>,<$Database2>.<Table2>;
      Note

      • <$Database1> and <$Database2> are database names. <Table1> and <Table2> are table names.

      • When you run the OPTIMIZE TABLE statement on the InnoDB storage engine, the following message is returned. This message is expected and can be ignored. The operation is successful if the output contains "ok". For more information, see OPTIMIZE TABLE Statement.

        Table does not support optimize, doing recreate + analyze instead

    Use DMS

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

    2. In the left-side navigation pane, select the instance ID of the target instance, double-click the target database, right-click any table name, and then select batch operation table.

    3. Select the tables for which you want to reclaim space, and then choose table maintenance > optimize table.优化表

    4. In the dialog box that appears, confirm that the information is correct and click OK.

    Related documents

    Reclaim space from table fragmentation

    FAQ

    Why is storage usage unchanged after OPTIMIZE TABLE?

    Problem

    After deleting data with the DELETE statement and running OPTIMIZE TABLE to reclaim tablespace, you query the DATA_FREE field in information_schema.tables and find that the value has not changed. This may suggest the operation failed and disk space was not released.

    Cause

    The disk space has been released, but MySQL table statistics are not updated promptly. This issue affects ApsaraDB RDS for MySQL 5.6, 5.7, and 8.0 instances with a minor engine version earlier than 20250531. In these versions, OPTIMIZE TABLE does not automatically update table and index statistics, so the DATA_FREE value in information_schema.tables retains the old value. For more information, see Bug #117426: optimize table does not update table and index stats.

    Solution

    • Recommended solution: Upgrade the minor engine version to 20250531 (for MySQL 8.0)

      This issue is fixed in the minor engine version 20250531 for ApsaraDB RDS for MySQL 8.0. After you upgrade, OPTIMIZE TABLE automatically refreshes the statistics, and the DATA_FREE field correctly reflects the actual space usage.

    • Temporary workaround: Force a statistics update
      If you cannot upgrade your database version in the short term, run the ALTER TABLE table_name ENGINE=InnoDB; statement on the optimized table. This forces the table to be rebuilt and its statistics to be updated. The DATA_FREE field in information_schema.tables then correctly reflects the reclaimed space.







    What to do if space is not released after a DELETE statement?

    In ApsaraDB RDS for MySQL, the DELETE statement only marks record locations or data pages as reusable without changing the disk file size. The tablespace is not immediately reclaimed, leading to storage fragmentation.

    You can reclaim space with native DDL commands or the lock-free schema change feature in DMS. Before using either method, ensure that your instance has sufficient disk space to prevent it from being locked.

    • Defragment space by using commands: Run DDL operations such as OPTIMIZE TABLE or ALTER TABLE <table_name> ENGINE=InnoDB; to reorganize table data and index structures and reclaim fragmented space.

      Important

      When using native DDL commands, perform them during off-peak hours to avoid metadata locks. For more information, see Usage notes.

    • Use the lock-free schema change feature in DMS: To avoid issues related to metadata locks, use this feature to reclaim space from storage fragmentation.

    What to do if space is not released after a TRUNCATE or DROP statement?

    If disk space is not released after you run a TRUNCATE or DROP operation, follow these steps:

    1. Confirm the space reclamation logic

      After you run a TRUNCATE or DROP statement, monitor the storage usage of the instance to confirm whether the space has been released. Typically, the decrease in storage usage reflects the size of the dropped table relative to the total instance space.

    2. Avoid relying on outdated information

      If you check the table size by using information_schema.tables or the ApsaraDB RDS console (Database Autonomy Service (DAS) > one-click diagnosis > space analysis), the displayed tablespace may not change due to data update delays. Rely on the instance's overall storage usage metric as the primary indicator.

    3. Impact of asynchronous deletion

      If the asynchronous deletion feature (such as Alibaba Cloud's Purge Large File Asynchronously feature) is enabled for your instance, the table file space is not released immediately. A background process gradually clears it, and disk space is released only after this process completes.