How to asynchronously delete large tables in PolarDB-X - PolarDB

Dropping a large table from a PolarDB-X instance removes all associated data files at once, which can destabilize the underlying POSIX file system. PolarDB-X provides an asynchronous deletion mechanism that removes these files gradually in the background, reducing I/O impact on the storage node.

How it works

Each storage node in a PolarDB-X instance runs the InnoDB storage engine. Instead of deleting all data files simultaneously when you drop a table, PolarDB-X uses a rename-then-delete approach:

Before deleting a tablespace, PolarDB-X renames the data files and marks them as temporary files.
A background thread asynchronously deletes the renamed files at a controlled rate.

PolarDB-X logs each file deletion operation to ensure the atomicity of Data Definition Language (DDL) statements.

Parameters

Run the following statement to view the current parameter settings:

SHOW GLOBAL VARIABLES LIKE '%data_file_purge%';

Example 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 parameter.

Parameter	Default	Description
innodb_data_file_purge	ON	Enables or disables asynchronous file deletion. When set to `ON`, data files from dropped tables are removed asynchronously in the background. When set to `OFF`, data files are deleted synchronously during the `DROP TABLE` operation.
innodb_data_file_purge_all_at_shutdown	OFF	Indicates whether all data files in the instance are deleted when the instance is shut down.
innodb_data_file_purge_dir	Empty	The directory where temporary files are stored during the deletion process. If left blank, the default data directory is used.
innodb_data_file_purge_immediate	OFF	Controls whether to unlink data files immediately without deleting them from disk. When set to `ON`, file links are removed but the underlying data is not immediately purged.
innodb_data_file_purge_interval	100	The interval between file deletion cycles, in milliseconds. A lower value increases the deletion rate. A higher value reduces I/O impact.
innodb_data_file_purge_max_size	128	The maximum size of a single file that can be deleted per cycle, in MB. Controls how much data is removed in each deletion pass.
innodb_print_data_file_purge_process	OFF	Indicates whether the system displays a progress bar to indicate the progress of file deletion.

Configure async file deletion

Run SET GLOBAL statements to adjust parameter values:

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

Note By default, PolarDB-X does not allow you to configure parameters by executing the SET GLOBAL statement. We recommend that you configure the parameters that are related to data storage in the console.

Monitor deletion progress

Query the information_schema.innodb_purge_files table to check the status of ongoing file deletions:

SELECT * FROM information_schema.innodb_purge_files;

Example 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.

Column	Description
log_id	The identifier of the deletion log entry.
start_time	The time when the asynchronous deletion started for this file.
original_path	The original file path of the data file before deletion.
original_size	The original size of the data file, in bytes.
temporary_path	The path of the temporary file created during the deletion process.
current_size	The current size of the temporary file, in bytes. This value decreases toward zero as the deletion progresses.