Problem description

In an ApsaraDB RDS for MySQL instance that runs MySQL 5.7, when I execute the following SQL statement to query the value of DATA_FREE, most of the large values of DATA_FREE are the same and are detected in some tables in the information_schema database. However, these values do not match the actual size of disk fragments.

SELECT TABLE_SCHEMA, TABLE_NAME, DATA_FREE FROM INFORMATION_SCHEMA.TABLES ORDER BY DATA_FREE DESC LIMIT 10; 

The following results are returned:

+--------------------+-----------------+-----------+
| TABLE_SCHEMA       | TABLE_NAME      | DATA_FREE |
+--------------------+-----------------+-----------+
| information_schema | COLUMNS         |   8388608 |
| information_schema | EVENTS          |   8388608 |
| information_schema | OPTIMIZER_TRACE |   8388608 |
| information_schema | PARAMETERS      |   8388608 |
| information_schema | PARTITIONS      |   8388608 |
| information_schema | PLUGINS         |   8388608 |
| information_schema | PROCESSLIST     |   8388608 |
| information_schema | ROUTINES        |   8388608 |
| information_schema | TRIGGERS        |   8388608 |
| information_schema | VIEWS           |   8388608 |
+--------------------+-----------------+-----------+

Causes

A bug exists in MySQL 5.7.

Some tables in the information_schema database, such as COLUMNS and EVENTS, use the InnoDB storage engine. These tables are temporary tables and belong to the innodb_temporary tablespace. The tablespace corresponds to the ibtmp1 file.

When you execute the preceding SQL statement to query the size of the disk fragments that are generated by the temporary tables, the system uses the disk fragment size of the ibtmp1 file as the value of DATA_FREE for the tables in the information_schema database instead of the size of the disk fragments generated by each table. The disk fragment size of the ibtmp1 file counts the size of the disk fragments that are generated by all tables in the file.

SELECT TABLESPACE_NAME, FILE_NAME, ENGINE, DATA_FREE FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary';

+------------------+-----------+--------+-----------+
| TABLESPACE_NAME  | FILE_NAME | ENGINE | DATA_FREE |
+------------------+-----------+--------+-----------+
| innodb_temporary | ./ibtmp1  | InnoDB |   8388608 |
+------------------+-----------+--------+-----------+

Solutions

This issue is caused by a bug in MySQL 5.7. You can ignore this issue. To prevent this issue, we recommend that you upgrade the major engine version of your RDS instance to MySQL 8.0. For more information, see Upgrade the major engine version of an ApsaraDB RDS for MySQL instance.

If you want to release the storage occupied by the disk fragments of the ibtmp1 file, you can restart your RDS instance. After the RDS instance is restarted, the storage occupied by the ibtmp1 file is automatically restored to the original value. The original value is specified by the innodb_temp_data_file_path parameter.