PolarDB: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.

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.

   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_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.
2. 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.