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, use one of the following methods based on the MySQL major version of the instance:

• MySQL 5.6: The temporary tablespace resides in the ibdata1 system tablespace. Restarting the instance does not release the temporary files. Compare the ibdata1 size of the primary and secondary nodes. If the secondary node has a smaller ibdata1, perform a high availability (HA) switchover and then rebuild the original secondary node to release the space. If both nodes have a large ibdata1, create a new instance and use DTS to migrate the data.

• MySQL 5.7: The temporary tablespace resides in the standalone file ibtmp1. Restarting the instance releases the temporary files. For high-availability instances, compare the ibtmp1 size of the primary and secondary nodes. If the secondary node has a smaller ibtmp1, perform an HA switchover and then restart the original secondary node to release the space. You can set the innodb_temp_data_file_path parameter to ibtmp1:12M:autoextend:max:10G to cap ibtmp1 at 10G. An instance restart is required for this change to take effect.

• MySQL 8.0 and 8.4: When the instance is locked, all user sessions are terminated. The sessions automatically begin to roll back after they are terminated. The rollback duration depends on the queries that were running. The instance storage is released after the transaction rollback completes.

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;

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.