ApsaraDB RDS:Troubleshoot insufficient storage issues on an ApsaraDB RDS for MySQL instance

View storage usage

The storage of the RDS instance is occupied by data in user databases, data in system databases, logs, and temporary tables and files. You can use the standard monitoring feature to view the storage usage of the RDS instance.

1. Log on to the ApsaraDB RDS console. Click the ID of the required RDS instance to go to the instance details page.

2. In the left-side navigation pane of the page that appears, click Monitoring and Alerts. On the page that appears, click the Standard Monitoring tab to view the storage usage in the MySQL Storage Space Used (MB) section.

3. Click the icon next to the section name to view the descriptions of metrics in the section. For more information, see View the monitoring information.

Solution overview

Insufficient storage is caused by the accumulation of various types of files. When you view the storage usage of your RDS instance, we recommend that you take note of the storage usage of the files and select a solution based on the file type. The following table describes the file types and the corresponding solutions.

You must also take note of the tablespace fragments and database indexes of your RDS instance.

• Tablespace fragment accumulation may cause insufficient storage. For more information, see Troubleshoot insufficient storage caused by tablespace fragment accumulation.

• If inappropriate indexes are configured for databases, index files are accumulated and the storage capacity may become insufficient. For more information, see Troubleshoot insufficient storage caused by index file accumulation.

Troubleshoot insufficient storage caused by temporary file accumulation

A large number of temporary files are written to your RDS instance, or a large number of binary log files for large transactions are cached in your RDS instance before the large transactions are committed. As a result, the storage capacity of your RDS instance is exhausted. Temporary files are generated when SQL statements that require sorting data, grouping data, and associating tables are executed. In this case, the system automatically locks your RDS instance to prevent data loss, and you cannot write data to your RDS instance.

Solution: For more information, see 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?

• RDS instance that runs MySQL 5.7 or earlier: We recommend that you restart your RDS instance to allow the system to automatically delete temporary files. For more information, see 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?

• RDS instance that runs MySQL 8.0: If the RDS instance is locked, all sessions are terminated and the system automatically performs a rollback. After the rollback is complete, the system automatically releases temporary files.

• If the RDS instance is not automatically unlocked after a long period of time, you must execute the SHOW PROCESSLIST statement to view the status of all sessions, identify the sessions that are in the Copy to tmp table and Sending data states, and then run the kill command to terminate the identified sessions.

Troubleshoot insufficient storage caused by binary log file accumulation

A large number of binary log files are generated within a short period of time after you execute large transactions, and the storage capacity of your RDS instance may be exhausted. Then, your RDS instance is automatically locked to prevent data loss. In this case, you cannot write data to your RDS instance.

Solution: For more information, see What do I do if the storage capacity of an ApsaraDB RDS for MySQL instance is exhausted by binary log files?

• Upload binary log files: You can use the Upload Binlogs feature to upload the binary log files of your RDS instance to an Object Storage Service (OSS) bucket. After the binary log files are uploaded, the files are automatically deleted from the RDS instance. For more information, see Manage binary log files.

• Modify the retention policy of binary log files: You can log on to the ApsaraDB RDS console and go to the Backup and Restoration page of your RDS instance. In the Local Log Retention Policy section of the Backup Strategy tab, you can modify the parameters of binary log files, including Retention Period, Maximum Storage Space Usage, and Available Storage Space. If the storage occupied by the binary log files of the RDS instance reaches the specified threshold, the system deletes earlier binary log files until the storage occupied by the binary log files falls below the threshold.

Troubleshoot insufficient storage caused by tablespace fragment accumulation

InnoDB manages tablespaces by page. If you execute the DELETE or UPDATE statement to delete or update data, the location or page of the deleted or updated data is marked as "reusable," but the size of the disk files remains unchanged and the tablespace is not reclaimed. If the original tablespace cannot be reused, a tablespace fragment is generated and occupies the storage of the RDS instance.

Solution: For more information, see How do I use the OPTIMIZE TABLE statement to release the tablespace of an ApsaraDB RDS for MySQL instance?

• Execute the required SQL statements to manage tablespace fragments: You can execute the OPTIMIZE TABLE statement to manage tablespace fragments.

• Use Data Management (DMS) to optimize tables: You can log on to the ApsaraDB RDS for MySQL instance by using DMS. In the left-side navigation pane of the DMS console, expand Instances Connected, right-click a table name, and then select Batch operation table. On the page that appears, select the table for which you want to manage tablespace fragments and choose Table Maintenance > Optimize Table.

• Enable the automatic fragment reclamation feature: You can go to the Autonomy Center tab of the Autonomy Services > Diagnostics page, click Autonomy Service Settings to enable the autonomy features, and then enable the automatic fragment reclamation feature on the Optimization and Throttling tab. For more information, see Use the automatic fragment reclamation feature.

Troubleshoot insufficient storage caused by system file accumulation

In most cases, large system files are mainly caused by a large undo file. 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 amount of undo data is generated, which occupies the storage and causes the storage capacity to be exhausted. To prevent data loss, the system automatically locks the RDS instance. The RDS instance enters the Locking state.

Solution: For more information, see What do I do if the storage capacity of an ApsaraDB RDS for MySQL instance is exhausted by system files?

• RDS instance that runs MySQL 8.0: The system automatically deletes undo files.

• RDS instance that runs MySQL 5.7:

  • If the value of the innodb_undo_tablespaces parameter is set to 2, the undo file of the RDS instance is stored in independent undo tablespaces and can be deleted. If the size of the undo file exceeds the value of the innodb_max_undo_log_size parameter and the logs in the undo file are no longer required by active transactions, the system truncates the undo file to release storage resources.

  • If the value of the innodb_undo_tablespaces parameter is set to 0, independent undo tablespaces are not used. The undo file is stored in the ibdata1 system tablespace and cannot be deleted. In this case, you can create an RDS instance that runs the required major engine version and then migrate data to the new RDS instance. You can also upgrade the major engine version of your RDS instance to MySQL 8.0. For more information, see Upgrade the major engine version.

• RDS instance that runs MySQL 5.5 or MySQL 5.6: You cannot delete undo files. In this case, we recommend that you upgrade your RDS instance to MySQL 5.7 on RDS High-availability Edition or MySQL 8.0.

  Note

  If the innodb_undo_tablespaces parameter for the RDS instance that runs MySQL 5.7 on RDS High-availability Edition is set to 2, the undo file of the RDS instance is stored in independent undo tablespaces and can be deleted.

Troubleshoot insufficient storage caused by general log accumulation

If you set the general_log parameter to ON, a table named mysql.general_log is generated and records all operations that are performed on the RDS instance and the operation details. The operations include select, insert, update, and delete operations. If a large number of queries are running or the mysql.general_log table is uncleared for a long period of time, the table consumes a large amount of storage resources. As a result, the storage capacity of the RDS instance is exhausted.

Solution: For more information, see FAQ about the general query log feature of ApsaraDB RDS for MySQL.

• Disable the collection of general logs: You can set the general_log parameter to OFF to prevent new logs from being generated. For more information, see Modify instance parameters.

• Delete general log files: You can log on to the RDS instance and execute the TRUNCATE TABLE mysql.general_log; statement to delete general log files.

• When you are debugging or tracing issues, we recommend that you set the general_log parameter to ON for a short period of time. After you resolve the issues, set the general_log parameter to OFF at the earliest opportunity.

Troubleshoot insufficient storage caused by user data file accumulation

If user data files are not deleted for a long period of time or tables in the files contain fields of the BLOB, TEXT, and LONG VARCHAR data types, a large amount of storage is occupied. As a result, the storage capacity is exhausted. In this case, your RDS instance is automatically locked to prevent data loss, and you cannot write data to the RDS instance.

Solution: For more information, see What do I do if an ApsaraDB RDS for MySQL instance is in the Locked state because its storage capacity is exhausted by data files?

• Delete data: You can execute the DROP or TRUNCATE statement to delete data that is no longer required.

• Compress data: You can compress large fields and then store the compressed fields in the database to reduce storage usage.

Troubleshoot insufficient storage caused by index file accumulation

Database indexes are stored as files on disks. If inappropriate indexes are configured for databases or a large number of secondary indexes are created, large index files are generated or index files are accumulated. As a result, the storage capacity may become insufficient.

Solution:

• Select appropriate fields to create indexes: In addition to primary key indexes, we recommend that you create indexes for fields that are frequently queried, need to be sorted, or are frequently used for inter-table joins to reduce storage usage. We also recommend that you create federated indexes instead of single-column indexes.

• Delete indexes: You can delete indexes that are not used for a long period of time or are no longer required to reduce the storage usage. You can also optimize the data structure of a table to reduce secondary indexes.