ApsaraDB RDS:What do I do if an ApsaraDB RDS for MySQL instance is in the Locked state because its storage capacity is exhausted by temporary files?

Problem description

Cause

A large number of temporary files are written to your RDS instance, or a large number of binary log files for large transactions are cached in your RDS instance before the large transactions are committed. As a result, the storage capacity of your RDS instance is exhausted. (Temporary files are generated when SQL statements that require sorting data, grouping data, and associating tables are executed.) In this case, ApsaraDB RDS for MySQL automatically locks your RDS instance to prevent data loss, and you cannot write data to your RDS instance.

Solution

In emergency situations, we recommend that you expand the storage capacity of your RDS instance. If you expand the storage capacity of your RDS instance, wait for about 5 minutes for the system to unlock the instance. For more information about how to upgrade the configurations of an RDS instance, see Change the specifications of an ApsaraDB RDS for MySQL instance.

If you cannot expand the storage capacity of your RDS instance, resolve the issue based on the major engine version of your RDS instance:

• If your RDS instance runs MySQL 5.7 or earlier, restart the RDS instance. For more information, see FAQ.
• If your RDS instance runs MySQL 8.0 and is locked, all sessions are terminated, and the transactions of the sessions are automatically rolled back. The duration required to roll back a transaction varies based on the queries that are executed by the sessions in the transaction. After all transactions are rolled back, the storage of the RDS instance is released.
  If your RDS instance is still locked, perform the following operations:

  1. Use Data Management (DMS) to connect to your RDS instance.
  2. Execute the following statement to view the sessions of your RDS instance:

     show processlist

  3. In the output, sort the sessions by State and check whether a large number of messages such as "Copy to tmp table" and "Sending data" are displayed in the State column. If a large number of the messages are displayed, record the IDs of the sessions for which the messages are returned.
  4. Execute the following SQL statement to terminate a session:

     kill [$ID];

     Note [$ID] indicates an ID that is obtained in the previous step. Before you terminate the session, make sure that your service is not affected.

What to do next

If an RDS instance is unlocked, perform the following operations to prevent the RDS instance from being locked again:

• Configure automatic storage expansion for the RDS instance. If the storage usage of the RDS instance reaches a threshold, the system automatically expands the storage capacity of the RDS instance. For more information, see Configure automatic storage expansion for an ApsaraDB RDS for MySQL instance.
• To prevent temporary files from occupying an excessive amount of storage, you can optimize SQL statements to prevent frequent use of the ORDER BY and GROUP BY clauses. You can also increase the values of tmp_table_size and max_heap_table_size based on your business requirements, but it is not recommended. The memory storage is more important than the disk storage. You can use EXPLAIN together with a SQL statement to check whether an internal temporary table is used. For example, you can check whether the message Using temporary exists in the Extra field. If yes, the internal temporary table is used.

  explain select * from alarm group by created_on order by default;

  The following system output is an example.
• To prevent cached binary log files from occupying an excessive amount of storage, you can reduce the number of large transactions, especially those that are run in parallel over multiple connections. If you want to run a large number of large transactions, you can increase the value of binlog_cache_size based on your business requirements. However, we recommend that you do not increase the value of binlog_cache_size to save disk storage. You can use short-lived connections to run large transactions to reduce the storage occupied by temporary files.
• We recommend that you monitor the disk usage, delete unnecessary data at the earliest opportunity, or perform data splitting. This helps ensure that the disk usage does not exceed 80%.

References

If your RDS instance is still in the Locked state after you delete temporary files, you can delete other types of files to reduce the disk usage. For more information, see following topics:

• What do I do if my ApsaraDB RDS instance is in the Locking state?
• What do I do if an ApsaraDB RDS for MySQL instance is in the Locked state because its storage capacity 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 system files?

FAQ

What do I do if the Restart Instance button is dimmed on the Basic Information page of my RDS instance?