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.

Procedure

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.

You can execute the following statements to configure the parameters:

   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.

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.