Enable Purge Large File Asynchronously in AliSQL to improve stability - ApsaraDB RDS

Dropping a table with a large InnoDB data file causes serious stability issues on POSIX file systems because the deletion blocks the file system synchronously. The Purge Large File Asynchronously feature, provided by AliSQL, solves this by moving the deletion to a background thread — your DROP TABLE call returns immediately, and the data file is removed incrementally in the background.

Use cases

Use this feature when your workload involves dropping tables with large InnoDB data files, where a direct synchronous deletion would cause latency spikes or database instability.

How it works

When you run DROP TABLE without this feature, InnoDB deletes the data file directly from the Portable Operating System Interface (POSIX) file system. For large files, this synchronous operation blocks I/O and destabilizes the database.

With Purge Large File Asynchronously enabled:

DROP TABLE renames the InnoDB data file to a temporary file and returns immediately.
A background thread deletes the temporary file incrementally at a configurable interval and chunk size.

Configure and monitor asynchronous purge

Check global variable settings

Run the following statement to view the current global variable settings:

SHOW GLOBAL VARIABLES LIKE '%data_file_purge%';

Sample output:

+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| innodb_data_file_purge                 | ON    |
| innodb_data_file_purge_all_at_shutdown | OFF   |
| innodb_data_file_purge_dir             |       |
| innodb_data_file_purge_immediate       | OFF   |
| innodb_data_file_purge_interval        | 100   |
| innodb_data_file_purge_max_size        | 128   |
| innodb_print_data_file_purge_process   | OFF   |
+----------------------------------------+-------+

The following table describes each global variable.

Variable	Default	Valid values	Description
`innodb_data_file_purge`	`ON`	`ON`, `OFF`	Whether to enable Purge Large File Asynchronously.
`innodb_data_file_purge_all_at_shutdown`	`OFF`	`ON`, `OFF`	Whether to delete all files when the host on which your RDS instance resides is shut down.
`innodb_data_file_purge_dir`	(blank)	Directory path	The directory where temporary files are stored during deletion.
`innodb_data_file_purge_immediate`	`OFF`	`ON`, `OFF`	Whether to retain data files and revoke only the links of the data files.
`innodb_data_file_purge_interval`	`100`	Integer	The interval between deletion operations. Unit: milliseconds.
`innodb_data_file_purge_max_size`	`128`	Integer	The maximum size of a single file that can be deleted. Unit: MB.
`innodb_print_data_file_purge_process`	`OFF`	`ON`, `OFF`	Whether to display a progress bar during file deletion.

To apply the recommended settings, run:

SET GLOBAL innodb_data_file_purge = ON;
SET GLOBAL innodb_data_file_purge_interval = 100;
SET GLOBAL innodb_data_file_purge_max_size = 128;

Monitor deletion progress

Run the following statement to check the current file deletion progress:

SELECT * FROM information_schema.innodb_purge_files;

Sample output:

+--------+---------------------+--------------------+---------------+-------------------------+--------------+
| log_id | start_time          | original_path      | original_size | temporary_path          | current_size |
+--------+---------------------+--------------------+---------------+-------------------------+--------------+
|      0 | 2021-05-14 14:40:01 | ./file_purge/t.ibd |     146800640 | ./#FP_210514 14:40:01_9 |     79691776 |
+--------+---------------------+--------------------+---------------+-------------------------+--------------+

The following table describes each column in the output.

Column	Description
`log_id`	The ID of the deletion task.
`start_time`	The time at which the deletion started.
`original_path`	The original path of the data file before deletion.
`original_size`	The original size of the data file. Unit: bytes.
`temporary_path`	The path of the temporary file currently being deleted.
`current_size`	The remaining size of the temporary file. Unit: bytes.