The storage usage of an RDS for MySQL instance is a key metric to monitor. Insufficient storage space can cause serious problems. For example, the database may become read-only, backups may fail, or storage scale-out tasks may take too long. This topic describes how to view storage usage and provides solutions for common storage issues.
View storage usage
The storage space of an RDS for MySQL instance includes user data, system data, various logs, and temporary files. You can view the storage usage using the standard monitoring feature.
Log on to the RDS console. Click the instance ID to go to the product page.
In the navigation pane on the left, choose Monitoring and Alerts > Standard Monitoring. You can view the instance storage usage in the MySQL Storage Usage view.
You can click the
icon next to the view title to see the description of each parameter. For more information, see View monitoring information.
Database system files and log files occupy storage space. The amount of space they use depends on factors such as the database payload and the data retention policy.
Solutions overview
Insufficient storage space is caused by the accumulation of various files in the instance. When you check the instance storage, focus on the space usage of the following files and choose a solution based on the file type.
File name | Parameter in Standard Monitoring | Parameter in Space Analysis | Solution |
Temporary file |
|
| |
Binlog file |
|
| Insufficient storage due to binary logging (local log) file accumulation |
undo log file |
|
| |
general log file |
|
| |
User data file |
|
|
In addition to the files listed above, you also need to check for instance fragmentation and database indexes:
The accumulation of fragmentation can also cause insufficient available storage space. For more information, see Insufficient storage due to fragmentation.
If the database uses an inefficient indexing policy, index files can accumulate and cause the instance to run out of space. For more information, see Insufficient storage due to index file accumulation.
In an emergency, we recommend that you first manually resize the disk to unlock the instance as soon as possible. After service is restored, you can then clean up the storage space based on file accumulation. In most cases, the instance is unlocked approximately 5 minutes after the disk is resized. The time required for resizing depends on the storage class and the amount of data. You can view the disk resizing progress in the Task Hub.
Insufficient storage due to 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 Solutions for temporary file accumulation in RDS for MySQL.
MySQL 5.7 or earlier: Restart the instance. The system automatically deletes the temporary files.
MySQL 8.0: When the instance is locked, it terminates all user sessions and starts an automatic rollback. The system automatically releases the temporary files after the rollback is complete.
If the instance is not automatically unlocked for a long time, run the
show processlistcommand to view the status of all sessions. Find sessions with a status such asCopy to tmp tableorSending data, and terminate them manually using thekillcommand.
Insufficient storage due to binary logging (local 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 Solutions for MySQL Binlog file accumulation.
One-click binary log upload: You can use the RDS One-click binary log upload feature to upload binary log files from an RDS instance to OSS. After the upload is complete, RDS automatically deletes the uploaded binary log files.
Modify the local log retention policy: In the RDS console, go to the Backup and Recovery page. Click the Backup Policy tab. Next to Local Log Retention Policy, click Edit. You can modify parameters such as the retention period, maximum storage usage, and available storage space. When the amount of local logs stored in the instance reaches the specified threshold, the system deletes the oldest local logs until the storage usage falls below the threshold.
Insufficient space due to fragmentation accumulation
InnoDB manages tablespaces by page. When you use delete or update statements to delete or update data, these statements only mark the record's location or data page as "reusable". The size of the disk file does not change. This means the space in the tablespace is not immediately reclaimed. If the original space cannot be reused, fragmentation occurs, which consumes storage space.
Solution: For more information, see Solutions for MySQL space fragmentation.
Defragment using the command line: Use the
optimize tablecommand to defragment the tablespace.Optimize data tables using DMS: Log on to the RDS for MySQL instance. In the DMS console, right-click any table name and choose Batch Operations. Select the tables that require defragmentation and choose .
Enable automatic fragment reclamation: In the RDS console, go to . Click Autonomy Feature Switch. After you enable the autonomy feature, go to the Optimization and Throttling tab and enable Automatic Fragment Reclamation. For more information, see Automatic fragment reclamation.
Insufficient storage due to 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 Solutions for MySQL system file accumulation.
MySQL 8.0: The system automatically cleans up undo files.
MySQL 5.7:
If the
innodb_undo_tablespacesparameter of the instance is set to2, the instance uses separate undo tablespaces to store undo data, and this data can be cleaned up. When the size of the undo files exceeds the value of theinnodb_max_undo_log_sizeparameter and the logs are no longer needed by any active transactions, the system performs atruncateoperation on the undo files to clean up the oversized files and release space.If the
innodb_undo_tablespacesparameter of the instance is set to0, the instance does not use separate undo tablespaces. The undo files are stored in the system tablespace ibdata1 and cannot be cleaned up. You can handle this in one of two ways:Method 1: Create a new instance and use DTS to migrate the data. For more information, see RDS for MySQL console operations (data migration).
Method 2: Upgrade the database version to MySQL 8.0. For more information, see Upgrade the database version.
MySQL 5.5 and MySQL 5.6: The cleanup of undo files is not supported. You must upgrade the major version to a MySQL 5.7 High-availability Edition instance or a MySQL 8.0 instance. After the upgrade, MySQL 5.7 can clean up undo files by enabling separate undo tablespaces. MySQL 8.0 supports automatic cleanup of undo files.
For MySQL 5.7 or earlier instances where separate undo tablespaces are not enabled (where innodb_undo_tablespaces = 0), the undo data written to ibdata1 cannot be released even after a major version upgrade. After the upgrade, only newly generated undo logs are written to separate tablespaces and support automatic cleanup. The space occupied by the original ibdata1 file cannot be reclaimed.
Insufficient storage due to general log file accumulation
If the general query log feature is enabled for your RDS instance, the general query log file records all user operations, including the execution details of each SQL statement, such as SELECT, INSERT, UPDATE, or DELETE. If a large number of access requests need to processed or the general query log file is not cleaned up for a long period of time, the size of the general query log file continuously increased. If you do not resolve the issue at the earliest opportunity, the storage capacity is exhausted.
Solution: For more information, see Solutions for MySQL general log file accumulation.
Disable general log collection: Disable the general log (set the runtime parameter to OFF) to prevent new logs from being generated. For more information, see Set instance parameters.
Delete general log files: Log on to the RDS instance and run the
TRUNCATE TABLE mysql.general_log;command to delete the general logs.Keep the general log disabled during routine database use. Enable it only temporarily for debugging or troubleshooting. After you are finished, promptly clean up the logs and disable the feature.
Insufficient storage due to user data file accumulation
User data files can cause the storage to become full if they are not cleaned up regularly. Using data types such as blob, text, and long varchar in the table schema also consumes a large amount of storage space. To prevent data loss, RDS for MySQL automatically locks the instance. After the instance is locked, you cannot perform write operations.
Solution: For more information, see Solutions for RDS for MySQL data file accumulation.
Delete unused data: Use the
droportruncatecommand to clean up unused data.Compress data: For large object data, you can compress the data before storing it in the database to reduce storage space usage.
Insufficient storage due to index file accumulation
Database indexes are stored as files on the disk. If you use an inefficient indexing policy or create many secondary indexes, the index files can accumulate and become too large. This can lead to insufficient instance storage space.
Solution:
Create indexes on appropriate fields: In addition to the primary key index, create indexes on fields that are frequently queried, require sorting, or are often used for table joins. Also, consider creating composite indexes instead of single-column indexes to save disk space.
Delete unused indexes: Consider deleting indexes that are no longer used or are redundant to save disk space. Also, optimize the data structure to reduce the number of secondary indexes.
NoteDeleting an index may cause blocking. Perform this operation during off-peak hours. You can also use the lock-free schema evolution feature of DMS to reduce the impact of the change.