Instance locked because disk is full of temporary files - ApsaraDB RDS

Symptoms

An ApsaraDB RDS for MySQL instance enters the Locked state when temporary files exhaust its disk space. The Running Status of an instance with the ID rm-***xez is Locked. The instance runs MySQL 5.7, has a 2-core CPU, supports up to 1,200 connections, is in the China (Hohhot) region, and was created on August 11, 2021.

Cause

Queries that sort, group, or join tables can create temporary table files that exhaust an instance's disk space. The binlog cache from large, uncommitted transactions can also cause this issue. To prevent data loss, ApsaraDB RDS automatically locks the instance. While locked, all write operations are blocked.

Resolution

In an emergency, increase the storage capacity of your instance. The instance is unlocked about five minutes after the storage is expanded. For more information, see Change Configuration.

If you cannot increase the storage capacity, use the method for your instance's major engine version:

MySQL 5.7 or earlier: Restart Instance.
MySQL 8.0: When an instance is locked, all user sessions are automatically terminated. After the sessions end, transactions begin to roll back. The time required for rollback depends on the queries that were running. The instance's storage space is released after the transactions are fully rolled back.
If the instance is not unlocked automatically after a long time, follow these steps:
1. Connect to the instance using Data Management (DMS).
2. Run the following SQL statement to view the database sessions:
```
show processlist
```
3. In the results, click the State column to sort the sessions. Look for sessions in states such as 'Copy to tmp table' or 'Sending data', and record their IDs. The command returns a process list with the Id, User, Host, db, Command, Time, and State columns. Use the Command and State columns to locate these problematic sessions.
4. Run the following SQL statement to terminate a session:
```
kill [$ID];
```
  Note
  In the statement, [$ID] is the session ID you recorded in the previous step. Important: Before you terminate a session, ensure this action does not affect your services.

Prevention

After the instance is unlocked, follow these steps to prevent the issue from recurring:

Configure automatic storage expansion. This feature automatically increases the instance's storage capacity when its storage runs low. For more information, see Configure automatic storage expansion.
Optimize SQL queries that create large temporary files. Avoid frequent use of ORDER BY and GROUP BY operations. You can increase the values of the tmp_table_size and max_heap_table_size parameters. However, increasing these values just to reduce disk usage is not recommended because memory is a more valuable resource than disk space. Run the EXPLAIN command on an SQL statement to check if it uses an internal temporary table. If the Extra field in the output contains Using temporary, the query uses an internal temporary table.
```
explain select * from alarm group by created_on order by default;
```
The following output is an example. In the results, a type value of ALL indicates a full table scan. The Extra column contains both Using temporary and Using filesort, which indicates that the query is also performing a file-based sort operation.
To manage the binlog cache, reduce large transactions, especially multiple large transactions running concurrently over different connections. If large transactions are common, you can increase the value of the binlog_cache_size parameter. However, we do not recommend adjusting this parameter solely to save disk space. Instead, use short-lived connections to run large transactions to reduce temporary space overhead.
Monitor your disk usage and clean up data or implement data sharding. Keep disk usage below 80%.

More information

If you cannot immediately clear temporary files to unlock the instance, you can free up disk space by clearing other types of files. For more information, see the following topics:

FAQ

Q: What do I do if the Restart Instance button is unavailable on the Basic Information page?
A: You can trigger a restart by following these steps:
1. Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the instance ID.
2. In the left-side navigation pane, click Parameters.
3. On the Editable Parameters tab, find and modify a parameter that has Yes in the Restart Required column.
Q: How can I locate the SQL statements that are generating a large number of temporary files?

A: To pinpoint the SQL statements generating excessive temporary files, follow these steps:
1. Enable the SQL Explorer and Audit feature.
2. Wait for the issue to recur. Then, use monitoring information to monitor temporary file generation and analyze the data captured by SQL Explorer and Audit.
3. In the execution logs from SQL Explorer and Audit, filter for SQL statements related to temporary file generation.