This topic describes how to use the Purge Large File Asynchronously feature to asynchronously delete large files from an ApsaraDB RDS instance that runs AliSQL. This feature helps ensure the stability of your database service.

Background information

If your RDS instance runs with the InnoDB storage engine, the deletion of large files from the instance compromises the stability of your POSIX file system. Therefore, InnoDB starts a background thread to asynchronously delete large files. Before InnoDB deletes a tablespace, InnoDB renames the data files in the tablespace to mark them as temporary files. Then, InnoDB asynchronously deletes the data files from the tablespace at low speeds.

Note AliSQL provides a log to record the file deletion operations. The log helps you ensure the atomicity of DDL statements.

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.