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:Jun 16, 2026

    When you work with large MySQL tables, running the DELETE statement does not immediately release disk space. Instead, it only marks database records or data pages as reusable. To reclaim the tablespace and reduce disk usage, you can run the OPTIMIZE TABLE command.

    Prerequisites

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

    • The instance's available disk space must be at least the size of the table you want to optimize.

      Note

      If your instance has insufficient disk space, you must first expand the disk space. Afterward, you can scale down the disk space, and the system will refund the price difference.

    Usage notes

    • Delete a large amount of data first: Running OPTIMIZE TABLE is ineffective unless you first use DELETE to remove a significant amount of data.

    • Temporary increase in disk space usage: When you run OPTIMIZE TABLE, MySQL creates a temporary table to store the reorganized data. This causes a brief increase in disk space usage. After the operation completes, the temporary table is deleted, and disk space usage returns to normal.

    • Table and index statistics may not change after space is reclaimed: Although the disk space is reclaimed, MySQL table statistics may not be updated immediately. For more information, see Why does the disk space 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 supports concurrent DML operations. However, running this operation on large tables can cause sudden spikes in I/O and buffer resource consumption, creating a risk of table locking or resource contention. During peak hours, this can even lead to instance unavailability or interruptions in monitoring. Therefore, we recommend that you perform this operation during off-peak hours to avoid affecting your business.

    • Manually terminating an OPTIMIZE TABLE operation: Pressing Ctrl+C in a database client, such as the MySQL command-line tool or a DMS SQL window, only disconnects the current client session. It does not stop the OPTIMIZE TABLE operation on the backend. To terminate the operation, you must establish another database connection, run SHOW PROCESSLIST; to find the thread ID of the OPTIMIZE TABLE operation, and then run KILL <thread ID>; to terminate that thread.

    Use the command line

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

    2. Use the DELETE statement to clean up unwanted data based on your business requirements.

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

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

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

      • When you run OPTIMIZE TABLE on an InnoDB table, the command returns the following message. This is expected behavior and can be ignored. The operation is successful if the final result is "ok". For more information, see the official MySQL documentation for OPTIMIZE TABLE Statement.

        Table does not support optimize, doing recreate + analyze instead

    Use DMS

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

    2. In the navigation pane on the left, select the target instance ID, double-click the database, right-click a target table, and then select Batch operation table.

    3. Select the tables that you want to optimize, and then choose Table Maintenance > Optimize Table.

    4. Verify the information in the dialog box and click OK.

    Related documents

    Reclaim fragmented table space

    FAQ

    Why is disk space unchanged after OPTIMIZE TABLE?

    Problem description

    After deleting a large amount of data and running OPTIMIZE TABLE as instructed in the ApsaraDB RDS for MySQL official documentation, a user may query the DATA_FREE field in information_schema.tables. If the value has not been updated, they might mistakenly assume the operation failed to reclaim disk space.

    Cause

    The space is reclaimed, but the table statistics are not updated promptly. This is a known issue in ApsaraDB RDS for MySQL 5.6, 5.7, and 8.0 (with a minor engine version earlier than 20250531). In these versions, running OPTIMIZE TABLE does not automatically update table and index statistics. As a result, the DATA_FREE value in information_schema.tables retains the old data and does not accurately reflect the actual space usage. For more details, see Bug #117426: optimize table does not update table and index stats.

    Solution

    • Recommended solution: Upgrade the minor engine version to 20250531 or later (for MySQL 8.0) to fix the issue where OPTIMIZE TABLE does not update the table status.

      The minor engine version 20250531 for ApsaraDB RDS for MySQL 8.0 fixes this issue. After the upgrade, OPTIMIZE TABLE automatically refreshes statistics, and the DATA_FREE field correctly reflects the actual space usage.

    • Workaround: Manually refresh statistics (for environments that cannot be upgraded immediately)
      If you cannot immediately upgrade your database version, you can run the ALTER TABLE table_name ENGINE=InnoDB; command on the tables where you previously ran OPTIMIZE TABLE. This forces a table rebuild and updates the statistics. After you run this command, the DATA_FREE field in information_schema.tables correctly shows the reclaimed space.































    What to do if space is not reclaimed after DELETE?

    In ApsaraDB RDS for MySQL, when you use the DELETE statement to remove data, the command only marks the record's location or the data page as reusable. The size of the disk file does not change, which means the tablespace is not reclaimed directly. This behavior leads to tablespace fragmentation and consumes instance storage space.

    You can use native DDL commands or the DMS lock-free schema change feature. Before you use either solution, ensure your instance has sufficient disk space to prevent instance locking due to storage exhaustion.

    • Reorganize fragmented 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, which reclaims fragmented space.

      Important

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

    • Use DMS lock-free schema change: To avoid metadata locks, you can use the DMS lock-free schema change feature to reclaim fragmented table space.

    What to do if space is not reclaimed after TRUNCATE or DROP?

    In ApsaraDB RDS for MySQL, if you find that disk space is not reclaimed after a TRUNCATE or DROP operation, follow these steps:

    1. Verify the space reclamation logic

      After you run TRUNCATE or DROP, check whether the space has been reclaimed by monitoring the instance's disk space usage. Typically, a drop in disk usage should correspond to the size of the deleted 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 (Autonomy Services > Diagnose > Storage Analysis), the information may be outdated due to data update delays. Therefore, we recommend that you rely on disk usage metrics as the most reliable indicator.

    3. Understand the impact of asynchronous deletion

      If your instance has an asynchronous deletion feature enabled, such as Alibaba Cloud's Purge Large File Asynchronously, the space is not released immediately. Instead, a background process gradually reclaims it. In this case, you must wait for the asynchronous process to complete before the disk space is fully reclaimed.