ApsaraDB RDS:RDS MySQL storage issues

Emergency response

If the instance is already locked (read-only), expand the disk immediately to restore write access. Run all cleanup and root-cause fixes after the service is restored.

1. In the RDS console, click the instance ID.

2. Manually resize the disk to unlock the instance.

The instance is unlocked within approximately 5 minutes after the resize completes. You can monitor progress in Task Center.

View storage usage

The storage space of an RDS for MySQL instance includes user data, system data, logs, and temporary files.

1. In the RDS console, click the instance ID.

2. In the left navigation pane, choose Monitoring and Alerts > Standard Monitoring.

3. Find the MySQL Storage Space Used view. Click the icon next to the view title to see a description of each parameter.

For detailed monitoring steps, see View the monitoring information.

Identify the cause

Check the MySQL Storage Space Used view and match the high-usage parameter to the file type and solution below.

Also check for fragmentation and index bloat, which do not appear as separate parameters but can consume significant space:

• Fragmentation: see Fragmentation accumulation

• Index bloat: see Index file accumulation

Temporary file accumulation

Temporary files are created when MySQL executes SQL statements that sort, group, or join large datasets. Binary logs for large transactions are also cached temporarily until the transaction commits. If these files accumulate, the instance locks.

Resolve the immediate lock:

• MySQL 5.7 or earlier: Restart the instance. The system deletes temporary files automatically on restart.

• MySQL 8.0: The instance terminates all user sessions and starts an automatic rollback. Temporary files are released after the rollback completes.

If the instance does not unlock automatically, run show processlist to identify sessions with a status of Copy to tmp table or Sending data, then terminate them with the kill command.

For more information, see Solutions for temporary file accumulation in RDS for MySQL.

Binary log file accumulation

Large transactions generate many binary log files in a short period. If logs accumulate faster than they are cleaned up, storage is exhausted and the instance locks.

Resolve by uploading existing logs:

Use the One-click binary log upload feature to upload binary log files from the RDS instance to OSS. RDS automatically deletes the uploaded files. See View and delete binary log files.

Prevent recurrence by adjusting the retention policy:

1. In the RDS console, go to the Backup and Restoration page.

2. Click the Backup Strategy tab.

3. Next to Local Log Retention Policy, click Edit.

4. Adjust the retention period, maximum storage usage, and available storage space. When local log storage reaches the threshold, the system deletes the oldest logs automatically.

For more information, see Solutions for MySQL binlog file accumulation.

Fragmentation accumulation

InnoDB manages tablespaces by page. When you delete or update rows using delete or update, MySQL marks the space as reusable but does not shrink the disk file. If the freed pages cannot be reused, fragmentation accumulates and consumes storage.

Resolve fragmentation using one of the following methods:

• Command line: Run optimize table <table_name> to rebuild the table and reclaim fragmented space.

• DMS console: Log on to the RDS instance in DMS. Right-click a table name and choose Batch operation table. Select the tables to defragment, then choose Table Maintenance > Optimize Table.

• Automatic reclamation: In the RDS console, go to Autonomy Services > Diagnostics > Autonomy Center. Click Autonomy Service Settings, then enable Automatic Fragment Reclamation on the Optimization and Throttling tab. For details, see Use the automatic fragment reclamation feature.

For more information, see Solutions for MySQL space fragmentation.

System file accumulation

Large system files are almost always caused by undo log growth. When long-running InnoDB queries run concurrently with large data updates, MySQL generates large amounts of undo data. This data fills the system tablespace and can exhaust storage.

The resolution depends on your MySQL version and undo tablespace configuration.

MySQL 8.0

The system automatically cleans up undo files. No manual action is required.

MySQL 5.7 with innodb_undo_tablespaces = 2

The instance uses separate undo tablespaces. When the undo file size exceeds innodb_max_undo_log_size and no active transactions require the data, the system runs a truncate operation to release the space.

MySQL 5.7 with innodb_undo_tablespaces = 0

Undo data is stored in the system tablespace ibdata1 and cannot be reclaimed. Use one of the following methods to resolve the issue:

• Method 1: Create a new instance and migrate data using DTS. See Data migration through the RDS MySQL console.

• Method 2: Upgrade to MySQL 8.0. See Upgrade the database version.

MySQL 5.5 and 5.6

Undo file cleanup is not supported. Upgrade to MySQL 5.7 High-availability Edition or MySQL 8.0. After upgrading to MySQL 5.7, enable separate undo tablespaces to support automatic cleanup. MySQL 8.0 cleans up undo files automatically.

For more information, see Solutions for MySQL system file accumulation.

General log file accumulation

When the general query log is enabled, RDS records every SQL statement executed, including SELECT, INSERT, UPDATE, and DELETE. Under high traffic, the log file grows rapidly. If not cleaned up regularly, it can exhaust storage.

Resolve immediately:

Run the following command to delete all existing general log records:

TRUNCATE TABLE mysql.general_log;

Stop new log growth:

Disable general log collection by setting the general_log runtime parameter to OFF. See Set instance parameters.

Best practice: Keep the general log disabled in production. Enable it temporarily only for debugging or troubleshooting, then disable it and clean up immediately after.

For more information, see Solutions for MySQL general log file accumulation.

User data file accumulation

User data files grow over time and can fill storage if not managed. Columns with blob, text, or long varchar types are common contributors to large data file sizes. When storage is full, RDS locks the instance to prevent data loss.

Resolve by cleaning up unused data:

• Use drop or truncate to remove tables or data that are no longer needed.

• For large object data, compress the data before storing it to reduce space usage.

For more information, see Solutions for RDS for MySQL data file accumulation.

Index file accumulation

Database indexes are stored as disk files. An inefficient indexing policy or a large number of secondary indexes can cause index files to grow and exhaust storage.

Optimize your indexing strategy:

• Create indexes on appropriate fields: Create indexes on fields that are frequently queried, sorted, or used in table joins. Use composite indexes instead of single-column indexes where possible to reduce total index file size.

• Remove unused indexes: Delete indexes that are redundant or no longer referenced by any queries.