What do I do if the storage capacity of an ApsaraDB RDS for MySQL instance is exhausted by system files? - ApsaraDB RDS

If an ApsaraDB RDS for MySQL instance is automatically locked when the size of system files, especially the undo file, is too large and the storage capacity of the RDS instance is exhausted, you can follow the instructions that are described in this topic to unlock the RDS instance and clean up the undo file.

Problem description

The storage capacity of an ApsaraDB RDS for MySQL instance is exhausted. The value of Instance Status for the RDS instance is Locking. On the Monitoring and Alerts page, the details of the storage usage show that the storage is occupied by a large undo file.

Causes

In most cases, large system files are mainly caused by a large undo file. If queries on the InnoDB table are running for a long period of time and a large amount of data is updated in the table during the queries, a large amount of undo data is generated, which occupies the storage and causes the storage capacity to be exhausted. To prevent data loss, the system automatically locks the RDS instance. The RDS instance enters the Locking state.

Note

This issue does not occur on an RDS instance that runs MySQL 8.0 because the system automatically deletes the undo file.

Solutions

Step 1: Unlock the RDS instance

You can unlock the RDS instance after you expand itsstorage capacity. For more information, see Change instance specifications.

Step 2: Delete the undo file

RDS instances that run MySQL 5.7:
Connect to the RDS instance and execute the following statement to query the value of the innodb_undo_tablespaces parameter. For more information, see Connect to an ApsaraDB RDS for MySQL instance.
```
SHOW VARIABLES LIKE 'innodb_undo_tablespaces';
```
- If the value of the innodb_undo_tablespaces parameter is 2, the undo data of the RDS instance is stored in independent undo tablespaces and can be deleted.
  Upgrade the minor engine version of the RDS instance to 20230228 or later and enable the innodb_undo_log_truncate parameter.
  When the size of the undo file exceeds the value of the innodb_max_undo_log_size parameter and the logs in the undo file are no longer required by active transactions, the system truncates the undo file to free up storage. For more information about how to configure the parameters of an RDS instance, see Modify instance parameters.
- If the value of the innodb_undo_tablespaces parameter is 0, independent undo tablespaces are not used. The undo file is stored in the ibdata1 system tablespace and cannot be deleted.
  If you want to clean up the undo data in the ibdata1 system tablespace, you can create an RDS instance and migrate the data of the original RDS instance to the new RDS instance. Alternatively, you can upgrade the major engine version of the RDS instance to MySQL 8.0. For more information, see Upgrade the major engine version.
  Important
  - If you upgrade the major engine version of an RDS instance, compatibility issues may occur. For more information, see the limits and differences between major engine versions in Upgrade the major engine version.
  - During the migration or upgrade of an RDS instance, an instance switchover occurs. We recommend that you perform the migration or upgrade during off-peak hours and make sure that your application is configured to automatically reconnect to the RDS instance. For information about the impacts of an instance switchover, see Impacts of an instance switchover.
RDS instances that run MySQL 5.5 or MySQL 5.6: The undo file of an RDS instance cannot be deleted. We recommend that you upgrade the major engine version of the RDS instance to MySQL 5.7 or MySQL 8.0. For more information, see Upgrade the major engine version.
If you want to upgrade the major engine version of the RDS instance to MySQL 5.7, set the Edition parameter to High-availability Edition. The value of the innodb_undo_tablespaces parameter in RDS High-availability Edition is 2. You can store and delete undo data in independent undo tablespaces.
Important
- If you upgrade the major engine version of an RDS instance, compatibility issues may occur. For more information, see the limits and differences between major engine versions in Upgrade the major engine version.
- During an upgrade, an instance switchover occurs. We recommend that you perform the upgrade during off-peak hours and make sure that your application is configured to automatically reconnect to the RDS instance. For information about the impacts of an instance switchover, see Impacts of an instance switchover.

What to do next

Optimize SQL statements to prevent slow SQL queries.
Perform operations such as index creation and deletion, table schema modification, table maintenance, and table deletion during off-peak hours.
Monitor and clean up the sessions or transactions that run for a long period of time. For more information, see View monitoring information.

References

You can also check and delete data files, temporary files, and binary log files to free up the storage of your RDS instance.
You can enable the autonomy service-related features to prevent RDS instances from being locked due to excessive undo file growth.