This topic describes how to resolve the issue that an ApsaraDB RDS for MySQL instance is locked due to the large size of the ibdata1 file.

Problem description

An RDS instance runs a major engine version of MySQL 5.5 or MySQL 5.6 or runs a major engine version of MySQL 5.7 with a minor engine version earlier than 20220101. The size of the ibdata1 file on the RDS instance may be large due to slow SQL queries. As a result, the storage of the RDS instance is exhausted. To prevent data loss, ApsaraDB RDS automatically locks the RDS instance.
Note This issue does not occur in an RDS instance that runs a major engine version of MySQL 5.7 with a minor engine version of 20220101 or later or that runs a major engine version of MySQL 8.0.

Solution

  • If your RDS instance runs MySQL 5.6, upgrade the storage capacity to unlock the RDS instance. For more information about how to upgrade the instance specifications, see Change the specifications of an ApsaraDB RDS for MySQL instance. If the storage capacity reaches the upper limit, submit a ticket to contact technical support to temporarily unlock the RDS instance. Then, you can perform subsequent operations.
  • If your RDS instance runs MySQL 5.5 or MySQL 5.7, submit a ticket to contact technical support to temporarily unlock the RDS instance. Then, you can perform subsequent operations.

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 last for a long period of time.