AliSQL uses the Purge Large File Asynchronously feature to improve the stability of your database service.

Background information

If you execute the DROP TABLE or TRUNCATE TABLE statement to delete data, the corresponding InnoDB data file is directly deleted from the file system. If the data file is large, the deletion of the data file causes serious stability issues in the Portable Operating System Interface (POSIX) file system. The Purge Large File Asynchronously feature starts a thread to asynchronously delete the data file. When you execute the DROP TABLE or TRUNCATE TABLE statement to delete data, the corresponding data file is saved as a temporary file, and the thread is started to asynchronously and smoothly delete the data file.

Procedure

  1. Execute the following statement to view the global variable settings of your RDS 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 these global variables.

    Variable Description
    innodb_data_file_purge Specifies whether to enable the Purge Large File Asynchronously feature.
    innodb_data_file_purge_all_at_shutdown Specifies whether to delete all files when the host on which your RDS instance resides is shut down.
    innodb_data_file_purge_dir Specifies the directory that stores temporary files.
    innodb_data_file_purge_immediate Specifies whether to retain data files and only revoke the links of the data files.
    innodb_data_file_purge_interval Specifies the intervals at which InnoDB deletes files. Unit: milliseconds.
    innodb_data_file_purge_max_size Specifies the maximum size of a single file that can be deleted. Unit: MB.
    innodb_print_data_file_purge_process Specifies whether to display the file deletion progress.
    Note We recommend that you configure the following variables to the values that are provided in the example: 
    set global INNODB_DATA_FILE_PURGE = on;
set global INNODB_DATA_FILE_PURGE_INTERVAL = 100;
set global INNODB_DATA_FILE_PURGE_MAX_SIZE = 128;
  2. Run the following command to view the file deletion 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 in the return result.
    Parameter Description
    start_time The point in time at which InnoDB starts to delete data files.
    original_path The original path in which the data files are stored before they are deleted.
    original_size The original size of the data files before they are deleted. Unit: bytes.
    temporary_path The path that stores the temporary files during the deletion progress.
    current_size The size of the remaining temporary files that are to be deleted. Unit: bytes.