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

Causes

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, the system automatically locks your RDS instance to prevent data loss, and you cannot write data to your RDS instance.

Solutions

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 specifications 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, you can restart the RDS instance to release temporary files. For more information, see Restart an ApsaraDB RDS for MySQL instance.

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 temporary files by State and check whether a large number of messages such as "Copy to tmp table" and "Sending data" exist in the State column of a session. If yes, a large number of temporary files are generated by the session. Then, record the ID of the session.

4. Execute the following SQL statement to terminate a session:

   kill [$ID];

   Note

   [$ID] indicates the 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 a large 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. However, this method is not recommended because the memory resources are more important than disk resources. You can use EXPLAIN together with an SQL statement to check whether an internal temporary table is used. For example, if the "Using temporary" message is displayed in the Extra column, an internal temporary table is used.

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

  The following figure shows an sample output.

• To prevent cached binary log files from occupying a large amount of storage, you can reduce the number of large transactions, especially large transactions 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 use this method. We recommend that you 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 you cannot delete temporary files to unlock your RDS instance, you can delete other types of files to reduce the disk usage. For more information, see following topics:

• What do I do if an ApsaraDB RDS instance is in the Locked 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?