All Products
Search
Document Center

RDS MySQL data files are full disk space cause "locked" status

Last Updated: May 19, 2022

Problem description

The ApsaraDB RDS for MySQL instance writes too much data, resulting in a full disk space of data files. As a result, the running status of the instance is locked.

Cause

An ApsaraDB RDS for MySQL instance may have full space because the data files have not been sorted for a long time. To avoid data loss, ApsaraDB RDS automatically locks the instance and cannot write data to it.

Solution

Take note of the following items:

  • Before you perform high-risk operations such as modifying the specifications or data of an Alibaba Cloud instance, we recommend that you check the disaster recovery and fault tolerance capabilities of the instance to ensure data security.
  • Before you modify the specifications or data of an Alibaba Cloud instance, such as an Elastic Compute Service (ECS) instance or an ApsaraDB RDS instance, we recommend that you create snapshots or enable backups for the instance. For example, you can enable log backups for an ApsaraDB RDS instance.
  • If you have granted specific users the permissions on sensitive information, such as usernames and passwords, or submitted sensitive information in the Alibaba Cloud Management Console, we recommend that you modify the sensitive information at the earliest opportunity.

Prerequisites

Check whether you can perform the procedure for your ApsaraDB RDS for MySQL instance version.

  • ApsaraDB RDS for MySQL 5.6, 5.7, or 8.0:
    Make sure that the kernel version is greater than or equal to 20190815. If the kernel version is earlier than 20190815, you can clean up data only after you upgrade the kernel version. For more information about how to upgrade the kernel version, see Upgrade the kernel version.
  • RDS for MySQL 5.5 instances:

    Submit a ticket to contact Customer Service to temporarily unlock the instance.

Limits

  • Before you run the clean data command, make sure that data is backed up to avoid loss.
  • The delete command in ApsaraDB RDS for MySQL cannot free up space. We recommend that you use the drop or truncate command to free up space.
  • The optimize command causes the table to be locked. We recommend that you perform this operation during off-peak hours.
  • There is a delay in cleaning data files. Please wait patiently for the used space of the instance to decrease.
  • Wait approximately 5 minutes for the system to unlock your RDS instance.

Procedure

According to the actual situation, choose the corresponding method to deal with.

Instance scale-out

You can scale out the disk space. For more information about scale-out operations, see the following documents to change the configuration:

Note: After a period of time (about 5 minutes), the RDS instance is unlocked.

Delete useless data

  1. Use DMS to connect to an ApsaraDB RDS for MySQL instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance.
  2. Execute the following SQL statement to view the file size of the database and confirm the historical data or useless data that can be deleted.
    SELECT file_name, concat(TOTAL_EXTENTS,'M') as 'FIle_size' FROM INFORMATION_SCHEMA.FILES order by TOTAL_EXTENTS DESC
  3. Use the drop or truncate command to clean up data.
    • drop: Use drop table [$Databases_Name].[$Table_Name] SQL statements to delete unnecessary tables.
      Note:
      • [$Databases_Name] is the name of the database.
      • [$Table_Name] is the name of the table.
    • truncate: Use truncate table [$Databases_Name].[$Table_Name] SQL statements to delete unnecessary tables.
  4. Wait approximately 5 minutes for the system to unlock your RDS instance.

What to do next

If the lock issue has been resolved, refer to the following steps to prevent the lock issue from occurring again:

  • When resources are insufficient, the storage capacity of the instance is automatically expanded. For more information, see Configure automatic storage capacity.
  • For tables that are frequently deleted, data holes are easily generated. We recommend that you use the following SQL statements to reclaim the space during off-peak hours. For more information about the optimize table command, see Release the tablespace of a MySQL instance.
    optimize table [$Databases_Name].[$Table_Name]

References

For more solutions to full instance space, see the following link:

Applicable scope

  • ApsaraDB RDS for MySQL