Asynchronously delete the data files associated with a large table

This topic describes how to asynchronously delete the data files associated with a large table.

Background information

By default, an underlying storage node of a PolarDB-X instance runs the InnoDB storage engine. If you delete a large table from such an instance, the data files that are associated with the table are deleted. This makes the POSIX file system that is mounted to the storage node unstable. In this case, InnoDB starts a backend thread to asynchronously delete data files. Before PolarDB-X deletes a tablespace, PolarDB-X renames the data files in the tablespace to mark them as temporary files. Then, PolarDB-X asynchronously deletes the data files from the tablespace at a low rate. Note PolarDB-X provides a log to record the file deletion operations. The log cab help you ensure the atomicity of DDL statements.


  1. Execute the following statement to view the global parameter settings of your instance:

    SHOW GLOBAL VARIABLES LIKE '%data_file_purge%';

The following code provides an example of the query result:

   | 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 the parameters.

Parameter Description
innodb_data_file_purge Indicates whether asynchronous deletion of data files is enabled.
innodb_data_file_purge_all_at_shutdown Indicates whether all data files in the instance are deleted when the instance is shut down.
innodb_data_file_purge_dir Indicates the directory for the temporary files.
innodb_data_file_purge_immediate Indicates whether the system disables the links between data files without deleting the files.
innodb_data_file_purge_interval Indicates the interval at which data files are deleted. Unit: milliseconds.
innodb_data_file_purge_max_size Indicates the maximum size of a single file that can be deleted. Unit: MB.
innodb_print_data_file_purge_process Indicates whether the system displays a progress bar to indicate the progress of file deletion.

You can execute the following statements to configure the parameters:

   set global INNODB_DATA_FILE_PURGE = on;
   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.

  1. Execute the following statement to view the progress of file deletion:

    select * from information_schema.innodb_purge_files;

The following code provides an example of the query result:

   | 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 the parameters in the query result.

Parameter Description
start_time The time when PolarDB-X starts to delete data files.
original_path The original path of the folder where the data files are stored before they are deleted.
original_size The original size of the data files before the deletion operation is performed. Unit: byte.
temporary_path The path of the folder where the temporary files are stored during the deletion process.
current_size The size of the temporary files that are to be deleted. Unit: byte.

results matching ""

    No results matching ""