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

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.

Solutions

Step 1: Unlock the RDS instance

You can unlock the RDS instance after you expand its storage 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.

    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 modify the parameters of an RDS instance, see Modify instance parameters.

    Note

    The innodb_undo_tablespaces parameter can be specified only when you create an RDS instance and cannot be modified after you create the RDS instance. If the value of the innodb_undo_tablespaces parameter is not specified when you create an RDS instance that runs MySQL 5.7, the parameter is set to 0 by default. In this case, you cannot update the minor engine version of the RDS instance to use independent undo tablespaces.

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

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

  • What do I do if the storage capacity of an ApsaraDB RDS for MySQL instance is exhausted by binary log files?

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

• You can enable the autonomy service-related features to prevent RDS instances from being locked due to excessive undo file growth.

  • Use the automatic SQL optimization feature for an ApsaraDB RDS for MySQL instance

  • Use the automatic SQL throttling feature

  • Configure automatic storage expansion