About SRGTechnical articleRecruitment Information
About SRGTechnical articleRecruitment Information
HOME/Technical article/I tried to add an index in Aurora MySQL, but it failed because the local storage ran out.

I tried to add an index in Aurora MySQL, but it failed because the local storage ran out.

This is Yuta Kikai (@fat47) from the Service Reliability Group (SRG) of the Media Management Division.
#SRGThe Service Reliability Group primarily provides comprehensive support for the infrastructure surrounding our media services, focusing on improving existing services, launching new ones, and contributing to open-source software (OSS).
This article describes an issue where adding an index using ALTER's online DDL (INPLACE) failed in an Aurora MySQL Version 2 environment because the RDS instance's local storage was exhausted.
 

Add index using online DDL

I added an index to a very large table in an environment running Aurora MySQL Version 2.
Since secondary index addition can be executed via online DDL (ALGORITHM=INPLACE), ALTER operations can be performed without locking writes.
 
 

However, the ALTER command failed to execute due to an error.

However, after waiting for a while during execution, it did not complete and the following error message was displayed.
 
When I checked the error log of the RDS instance, I found the following output:
 
I'm getting an error message saying there isn't enough storage space.

Why do we fail?

The cluster volume used to store Aurora's database data will automatically expand to a size of 128 TiB.
 
However, the disk space shortage this time is not in the cluster volume, but in the "local storage" area of ​​the RDS instance.
This is a storage area with a fixed capacity for each RDS instance size.
 
The table above is partially quoted from the document mentioned above.
DB instance classMaximum temporary/local storage available (GiB)
db.r6g.8xlarge640
db.r6g.4xlarge320
db.r6g.2xlarge160
db.r6g.xlarge80
db.r6g.large32
The environment used for this execution is db.r6g.large, so the local storage size is32GiBThis is the result.
Let's check the "FreeLocalStorage" metrics in CloudWatch when the process was executed.
It turned out that the error occurred when the value approached 0 to a certain extent.
*Because the ALTER command was executed twice, this metric shows the point where it approaches 0 twice.
 
When creating a secondary index with ALGORITHM=INPLACE in online DDL, a log file is created that records the DML operations performed during DDL execution,
Temporary files, such as sort files, are created. These temporary files use local storage, and it appears the query failed because this storage space became insufficient.
 

Countermeasures

There are three possible solutions.
  • Increase the instance size to increase the local storage capacity.
  • Specify the algorithm to use when executing ALTER TABLE as ALGORITHM=COPY.
  • Use the RDS B/G Deployments feature to add an index to the Green cluster only using ALGORITHM=COPY.
 

Increase the instance size to increase the local storage capacity.

This is the method we used to handle the situation this time.
By changing from db.r6g.large to db.r6g.xlarge, the local storage capacity increased.80GiBThis is what happened.
 
When I ran the same ALTER command again in that environment, it completed successfully without exhausting local storage.
 

Algorithm when executing ALTER TABLEALGORITHM=COPYSpecify

Executing ALGORITHM=COPY will prevent local storage exhaustion.
However, since the table is locked until execution is complete, it cannot accept DML updates simultaneously.
We decided against it this time because it would require putting the system into maintenance mode or similar measures to ensure that other update queries can be stopped without causing problems.

Use the RDS B/G Deployments feature only for green clusters.ALGORITHM=COPYAdd index

This method involves performing the above steps on a Green cluster created using the RDS Blue/Green Deployments feature, and then switching between B and G clusters.
In this case, since there are no data accesses from the service to the Green cluster, it is not a problem if the table is locked until execution is complete.
 
However, it's important to note that there will be a downtime of about one minute when switching between B/G modes.
 

In conclusion

Online DDL may fail due to limitations in the specifications of Aurora MySQL.
It's essential to be familiar with the limitations of local storage and learn to work with them effectively.
 
SRG is looking for new team members. If you are interested, please contact us here.