tencent cloud

All product documents
TencentDB for MySQL
DocumentationTencentDB for MySQLKernel FeaturesStability FeaturesAsync Deletion of Big Tables
Async Deletion of Big Tables
Download PDF
Last updated: 2025-04-16 17:29:32
Async Deletion of Big Tables
Last updated: 2025-04-16 17:29:32
Download PDF

Overview

This feature is used to drop tables with large data files to avoid I/O fluctuation.
When the DROP TABLE operation is performed, the system will first rename the original database file (.ibd) to make a new temporary file and then returns the message of successful operation promptly. The temporary file is stored in the directory specified by the innodb_async_drop_tmp_dir parameter and is truncated in batches by the system in the backend. The size of the file to be truncated each time is specified by the innodb_async_truncate_size parameter (supported in MySQL 5.7 and 8.0 but not 5.6).
Users do not need to perform this operation. Instead, it is automatically completed by the kernel. Principle: A hard link is created in another directory for the data file of a table when the table is dropped. As a result, when DROP TABLE is executed, only the hard link to the file is deleted. After that, the backend thread will scan files that need to be deleted in the hard-linked directory and automatically truncate the data file of the dropped table.

Supported Versions

Kernel version: MySQL 5.6 20220303 and later.
Kernel version: MySQL 5.7 20220715 and later.
Kernel version: MySQL 8.0 20200630 and later.

Use Cases

This feature is used to drop tables with large data files.

Instructions

For MySQL 5.6, you can set the innodb_async_truncate_work_enabled parameter to ON to enable the async mode of DROP TABLE. The default value is OFF.
For MySQL 5.7 and 8.0, you can set the innodb_table_drop_mode parameter to ASYNC_DROP to enable the async mode of DROP TABLE. The default value is ASYNC_DROP.
The size of the file to be truncated each time is specified by the innodb_async_truncate_size parameter. This is not supported for MySQL 5.6.
You can make the async drop of big tables more efficient by enabling the innodb_fast_ddl parameter as instructed in FAST DDL.
Instructions for MySQL 5.6
Instructions for MySQL 5.7 and 8.0

Steps to Enable the Feature

1. Before you use the feature of asynchronous big table deletion, set the parameter innodb_adaptive_hash_index to OFF.
2. Set the parameter innodb_async_truncate_work_enabled to ON to enable the feature of asynchronous big table deletion. For parameter settings, see Setting Instance Parameters.

Relevant Parameters

Parameter Name
Dynamic
Type
Default
Value Range
Description
innodb_adaptive_hash_index
yes
string
ON
ON/OFF
Whether to enable InnoDB adaptive hash index.
ON: Enable.
OFF: Disable.
innodb_async_truncate_work_enabled
yes
string
OFF
ON/OFF
Whether to enable asynchronous big table deletion.
ON: Enable.
OFF: Disable.

Steps to Enable the Feature

1. Before you use the feature of asynchronous big table deletion, set the parameter innodb_fast_ahi_cleanup_for_drop_table to ON.
2. Set the parameter innodb_adaptive_hash_index to OFF.
3. Set the parameter innodb_table_drop_mode to ASYNC_DROP to enable the feature of asynchronous big table deletion. For parameter settings, see Setting Instance Parameters.
4. (Optional) Set the parameter innodb_fast_ddl to ON, which can make asynchronous big table deletion more efficient. The FAST DDL feature and relevant parameters are involved. For details, see FAST DDL.

Relevant Parameters

Parameter Name
Dynamic
Type
Default
Value Range
Description
innodb_fast_ahi_cleanup_for_drop_table
yes
string
ON
ON/OFF
Whether to enable quick cleanup optimization for adaptive hash indexes.
ON: Enable. Once it is enabled, it can prevent big table deletion from being blocked due to slow hash index cleanup.
OFF: Disable.
innodb_adaptive_hash_index
yes
string
ON
ON/OFF
Whether to enable InnoDB adaptive hash index.
ON: Enable.
OFF: Disable.
innodb_table_drop_mode
yes
string
ASYNC_DROP
SYNC_DROP/ASYNC_DROP
Whether to enable asynchronous big table deletion.
ASYNC_DROP: Asynchronous mode. This value indicates that asynchronous big table deletion is enabled.
SYNC_DROP: Synchronous mode. This value indicates that asynchronous big table deletion is disabled.
innodb_async_truncate_size
yes
int
128
128 - 168
Size of the file to be truncated each time in the backend during asynchronous big table deletion, in MB.
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No

Feedback

Contact Us

Contact our sales team or business advisors to help your business.

Contact Us
Technical Support

Open a ticket if you're looking for further assistance. Our Ticket is 7x24 available.

Submit a Ticket
7x24 Phone Support
Hong Kong, China
+852 800 906 020 (Toll Free)
United States
+1 844 606 0804 (Toll Free)
United Kingdom
+44 808 196 4551 (Toll Free)
Canada
+1 888 605 7930 (Toll Free)
Australia
+61 1300 986 386 (Toll Free)
EdgeOne hotline
+852 300 80699
More local hotlines coming soon