When a MySQL table contains a large amount of data, cleaning up data with the DELETE statement does not directly release disk space. Instead, it only marks database records or data pages as reusable. To actually reclaim table space and reduce disk usage, you can use OPTIMIZE TABLE.
Prerequisites
Only the InnoDB and MyISAM engines support the OPTIMIZE TABLE statement.
The remaining disk space of the instance must be greater than or equal to the space occupied by the table to be released.
Note:
If the remaining disk space of the instance is insufficient, make sure to scale out the disk space first. After subsequent operations are completed, you can scale in the disk space as needed. The system then calculates the unused resources and issues a refund. Precautions
Delete a large amount of data first: If you do not delete a large amount of data with DELETE first, directly running OPTIMIZE TABLE cannot effectively reduce table space utilization.
Temporary increase in disk space usage: When you run OPTIMIZE TABLE, MySQL creates a temporary table to store the reorganized data, which causes disk space usage to increase for a short period. After the operation is completed, the temporary table is deleted and disk space usage returns to normal.
The statistics of the table and indexes may remain unchanged after the release: This is because the MySQL table statistics are not refreshed in a timely manner. For details, see FAQs. Performance impact and peak-hour risks: In TencentDB for MySQL 5.7 and 8.0, OPTIMIZE TABLE is executed using Online DDL and supports concurrent DML operations. However, running this operation on a big table may cause sudden IO and Buffer resource consumption, posing risks of table locking or resource contention. During peak hours, it may also lead to instance unavailability or monitoring interruptions. Therefore, it is recommended to run this operation during off-peak hours to avoid impacting normal business.
Manually terminate a running OPTIMIZE TABLE operation: Pressing Ctrl + C in a client (such as the MySQL command line or the SQL window of DMC) only disconnects the current client connection and does not terminate the OPTIMIZE TABLE operation running on the backend. To terminate the operation, run SHOW PROCESSLIST; through another database connection to view the thread list, find the thread ID of the OPTIMIZE TABLE operation in progress, and then run KILL <thread ID>; to terminate the thread.
Note that for MySQL 8.0 from version 20221215 (inclusive) to version 20230703 (inclusive), MySQL table rebuild operations (ALTER/OPTIMIZE) may cause data loss. For details, see documentation. Operations via the CLI
2. Use the DELETE statement to clean up unnecessary data based on your actual business needs.
3. Run the OPTIMIZE TABLE command to release table space.
OPTIMIZE TABLE <$Database1>.<Table1>,<$Database2>.<Table2>;
Note:
1. <$Database1> and <$Database2> are database names, and <Table1> and <Table2> are table names.
2. When you run the OPTIMIZE TABLE statement on the InnoDB engine, the following message appears. This message is a normal result of the operation, so you can ignore it and simply confirm that ok is returned. For details, see OPTIMIZE TABLE Statement. Table does not support optimize, doing recreate + analyze instead
FAQs
Why Doesn't Disk Space Change After OPTIMIZE TABLE Is Run on TencentDB for MySQL?
Issue Description
After a user deletes a large amount of data with DELETE and runs OPTIMIZE TABLE to reclaim table space, they immediately query the DATA_FREE field in information_schema.tables and find that the value has not been updated. As a result, they assume that disk space has not been released and that the reclaim operation is ineffective.
Fault Cause
The actual disk space has been released, but this is because MySQL table statistics are not refreshed in a timely manner. After OPTIMIZE TABLE is executed, the statistics of the table and indexes are not automatically updated. As a result, the DATA_FREE value in information_schema.tables retains the old value and cannot accurately reflect the actual space usage. For details about this issue, see Bug #117426. Solution
Temporary workaround: force refresh of statistics
You can run the command ALTER TABLE table_name ENGINE=InnoDB; on a table that has already undergone OPTIMIZE TABLE to force a rebuild of the table and update its statistics. After that, the DATA_FREE value in information_schema.tables will correctly show the space that has been released.
Note:
Note that for MySQL 8.0 from version 20221215 (inclusive) to version 20230703 (inclusive), MySQL table rebuild operations (ALTER/OPTIMIZE) may cause data loss. For details, see documentation. What Should I Do If Disk Space Is Not Released After DELETE Is Executed on TencentDB for MySQL?
In TencentDB for MySQL, when you delete data with the DELETE statement, the command only marks the positions of records or data pages as reusable. The size of the disk file does not change, so table space is not directly reclaimed. This behavior prevents table space from being directly reclaimed, resulting in fragmented instance storage space and increased storage usage.
Note that before running the operation, you must ensure that the instance has sufficient remaining space to prevent the instance from being locked due to full storage.
To reclaim space fragments, run DDL operations such as OPTIMIZE TABLE or ALTER TABLE <table_name> ENGINE=InnoDB; to reorganize table data and index structures, thereby releasing fragmented space.
Important
When using native DDL commands, run them during off-peak hours to avoid blocking caused by metadata locks. For more important notes, see Precautions. What Should I Do If Disk Space Is Not Released After TRUNCATE or DROP Is Run on TencentDB for MySQL?
In TencentDB for MySQL, if disk space is not released after you run a TRUNCATE or DROP operation, handle the issue by following these steps:
1. Confirm the space release logic
After running TRUNCATE or DROP, check monitoring metrics to confirm whether space has been released. Typically, the proportion of the deleted table size to the total instance space is reflected as decreases in storage space usage, data file usage, storage space utilization, and data file space utilization.
2. Avoid relying on outdated information
If you view the table size through information_schema.tables or the console (DBbrain > Diagnostics & Optimization > Space Analysis), the table space may appear unchanged due to data update delays. Therefore, it is recommended to prioritize disk utilization as the basis for judgment.
3. Impact of Asynchronous Deletion
If the instance has the asynchronous big table deletion capability enabled, the space occupied by table files will not be released immediately. Instead, it will be gradually cleaned up by background processes. In this case, you need to wait for the asynchronous process to complete before the disk space is finally released.