Resolve the issue of an RDS for MySQL instance being automatically locked due to exhausted storage - ApsaraDB RDS

When the disk space of an ApsaraDB RDS for MySQL instance is exhausted because of issues such as slow SQL statements or excessive data insertion, the system automatically locks the instance to prevent data loss. The instance status changes to "Locked". While the instance is locked, you cannot perform write operations on it. This topic describes how to delete data files, temporary files, binary logging files, undo files, and general_log files to free up storage space.

Causes

The storage of an instance may be exhausted for the following reasons:

High usage of data files: A large volume of data is written to the database, causing data files to occupy excessive disk space.
High usage of log files: If a log backup policy is not configured correctly, large transactions can cause log files to grow rapidly.
High usage of temporary files: Temporary files can consume a large amount of space. These include temporary table files generated by sorting, grouping, or join operations in query statements, and log cache files for large, uncommitted transactions.
High usage of system files: This is often caused by oversized undo files. If a long-running query is performed on an InnoDB table while a large amount of data in that table is being modified, the system generates a large volume of undo information that consumes significant storage space. RDS for MySQL 8.0 automatically deletes undo files, so this issue is uncommon. However, you should still be aware of special cases.
High usage of the general_log file: When general logging is enabled, the general_log file records all operations, including the execution details of each SQL statement. If the access volume is high or the file is not cleared regularly, it can consume a large amount of storage space. You can use the following method to check the size of the general_log file.
1. Check the storage usage of the instance to determine whether the sys_data_size file is too large.
2. Check whether general logging is enabled for the instance (the general_log parameter is set to ON).
3. Connect to an RDS for MySQL instance and execute the following statement to query the size of the general_log file:
```
SELECT table_schema AS 'Database', table_name,SUM(data_length + index_length + data_free)/1024/1024 AS "Table size in MB",SUM(DATA_FREE)/1024/1024 AS "Fragmentation size in MB"
FROM information_schema.TABLES
WHERE table_name='general_log'
```
  Note
  - The preceding SQL statement retrieves the size of the mysql.general_log table from the TABLES table in the information_schema database and converts the value to MB.
  - The data retrieved by the preceding SQL statement is sample data and may differ from the actual data.

Solutions

You can follow these steps to identify the cause of the issue and resolve the lock on your ApsaraDB RDS for MySQL instance that was caused by insufficient storage space.

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.
In the navigation pane on the left, click Monitoring and Alarms to view the file types that use storage space.
Select a solution based on your actual condition.
Note
After you clear the disk space, the RDS instance is typically unlocked within 5 to 15 minutes.