ApsaraDB RDS:What do I do if my ApsaraDB RDS for MySQL instance is automatically locked due to exhausted storage capacity?

Problem description

Causes

The following issues may cause the storage capacity of an RDS instance to be exhausted:

• A large number of data files are stored.

• A large number of log files are stored.

  If the log backup policy is inappropriate, a large number of log files may be generated because large transactions are executed.

• A large number of temporary files are stored.

  A large number of temporary files that are generated from sorting, grouping, and join operations in SQL queries, or a large number of log files that are cached before large transactions are committed.

• A large number of system files are stored.

  In most cases, large system files are mainly caused by large undo files. If queries on the InnoDB table are running for a long period of time and a large amount of data is updated in the table during the queries, a large number of undo files are generated, which occupies a large amount of storage.

  Note

  This issue does not occur on an RDS instance that runs MySQL 8.0 because the system automatically deletes undo files.

• The size of the general_log file is large.

  If the general_log parameter is set to ON, a general_log file is generated to record all operations that are performed on the RDS instance and the operation details. The operations include query, insert, update, and delete operations. If queries are running for a long period of time or the general_log file is not cleared for a long time, a large amount of data is generated. The data occupies storage and causes the storage capacity to be exhausted. You can perform the following operations to query the size of the general_log file:

  1. Check the storage usage of the RDS instance. You can check whether the sys_data_size file is excessively large. For more information, see View the monitoring information.

  2. Check whether the general_log parameter is set to ON. For more information, see View the parameters of an ApsaraDB RDS for MySQL instance.

  3. Connect to the RDS instance and execute the following statement to check whether the general_log file is excessively large. For more information, see Connect to an ApsaraDB RDS for MySQL instance.

     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 "Fragment size in MB"
     FROM information_schema.TABLES
     WHERE table_name='general_log'

     Note

     • The SQL statement is used to query the data of the mysql.general_log table from the TABLES table in the information_schema database. The data size is displayed in MB.

     • The data that is returned by this SQL statement is sample data. The actual data returned varies.

Solutions