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?

Option 1: Expand storage capacity

Expand the storage capacity of the instance. After expansion, the system automatically unlocks the instance within approximately 5 minutes. For details, see Change the specifications of an ApsaraDB RDS for MySQL instance.

Option 2: Restart the instance to release temporary files

If storage expansion is not possible, restart the instance. A restart clears temporary files and typically unlocks the instance.

If the Restart Instance button on the Basic Information page is dimmed, use this workaround:

1. Go to the Instances page. In the top navigation bar, select the region where the instance resides, then click the instance ID.

2. In the left-side navigation pane, click Parameters.

3. On the Editable Parameters tab, find any parameter that shows Yes in the Force Restart column, and change its value.

Option 3: Identify and kill the offending session

If the instance is still locked after a restart, identify the session generating temporary files and terminate it.

1. Connect to the instance using Data Management (DMS).

2. Run the following statement to list active sessions:

   show processlist

3. Sort the results by the State column. Look for sessions where State shows Copy to tmp table or Sending data — these indicate heavy temporary file usage. Record the ID of the offending session.

   

4. Terminate the session:

   kill [$ID];

   Replace [$ID] with the session ID from the previous step.

   Important

   Before terminating a session, verify that doing so will not affect running services.

Enable automatic storage expansion

Configure automatic storage expansion so the system expands storage automatically when usage approaches the threshold. For details, see Configure automatic storage expansion for an ApsaraDB RDS for MySQL instance.

Optimize SQL statements that generate temporary files

To check whether a specific SQL statement uses a temporary table, run EXPLAIN and examine the Extra column. If Using temporary appears, the statement generates a temporary table.

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

The following figure shows a sample output.

To reduce temporary file usage:

• Rewrite queries to avoid frequent ORDER BY and GROUP BY on large result sets.

• Add appropriate indexes to avoid full-table sorts.

• You can increase the values of tmp_table_size and max_heap_table_size based on your business requirements. However, this method is not recommended because memory resources are more important than disk resources.

Reduce temporary storage from large transactions

Large transactions write binlog cache files to disk while they are in progress. To reduce this:

• Break large batch operations into smaller transactions.

• Avoid running multiple large transactions in parallel across many connections.

• Use short-lived connections for large transactions to reduce the storage occupied by temporary files.

• You can increase the value of binlog_cache_size based on your business requirements. However, we recommend that you do not use this method.

Monitor disk usage

Keep disk usage below 80%. Set up monitoring alerts so you can delete unnecessary data or split large datasets before storage is exhausted.