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 20230601 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
|
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
|
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 | OFF | 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. |