Asynchronously delete large tables

Background information

The storage nodes of PolarDB-X use the InnoDB engine by default. Directly deleting a large table also deletes its data files, which can cause critical stability issues in the POSIX file system. To prevent this, InnoDB starts a background thread to asynchronously purge the data files. When a tablespace is deleted, its data file is renamed to a temporary file. A purge thread then slowly and asynchronously purges the temporary file.

Procedure

1. Run the following command to view the global variable settings of the instance:

   SHOW GLOBAL VARIABLES LIKE '%data_file_purge%';

   The following result is returned:

     +----------------------------------------+-------+
     | 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	Specifies whether to enable the asynchronous purge policy.
   innodb_data_file_purge_all_at_shutdown	Purges all files during a normal shutdown.
   innodb_data_file_purge_dir	The folder for temporary files.
   innodb_data_file_purge_immediate	Unlinks the data files but does not purge them.
   innodb_data_file_purge_interval	The purge interval. Unit: ms.
   innodb_data_file_purge_max_size	The maximum size of a single file that can be purged at a time. Unit: MB.
   innodb_print_data_file_purge_process	Specifies whether to print the file purge process.

   Run the following commands to set 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, you cannot directly set parameters in PolarDB-X using the set global instruction. You must set the storage layer parameters in the console.
2. Run the following command to view the purge progress:

   select * from information_schema.innodb_purge_files;

   The following result is returned:

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

   Parameter	Description
   start_time	The start time of the purge operation.
   original_path	The original path of the data file.
   original_size	The original size of the data file. Unit: byte.
   temporary_path	The path of the temporary file being purged.
   current_size	The remaining size of the temporary file to be purged. Unit: byte.