About SRGArticlesRecruitment Information
About SRGArticlesRecruitment Information
HOME/Articles/When I tried to add an INDEX to Aurora MySQL, it failed because the local storage was exhausted.

When I tried to add an INDEX to Aurora MySQL, it failed because the local storage was exhausted.

This is Yuta Onkai (@fat47) from the Service Reliability Group (SRG) of the Media Headquarters.
#SRG(Service Reliability Group) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
This article summarizes an issue where adding an INDEX using ALTER online DDL (INPLACE) in an Aurora MySQL Version 2 environment failed due to the local storage of the RDS instance being depleted.
 

Add INDEX with online DDL

In an environment running Aurora MySQL Version 2, I added an index to a large table.
Adding a secondary index can be performed using online DDL (ALGORITHM=INPLACE), so ALTER can be performed without locking writes.
 
 

However, ALTER execution failed due to an error.

However, after waiting for a while, the following error message was displayed instead of completing the process.
 
When I check the error log for the RDS instance, I see the following output:
 
I am getting an error saying there is not enough space.

Why it fails

The cluster volume used to store Aurora DB data will automatically expand in size up to 128 TiB.
 
However, this time the disk space shortage is not in the cluster volume, but in the "local storage" area of the RDS instance.
This is a fixed amount of storage space for each RDS instance size.
 
Here is an excerpt from the table 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 this time is db.r6g.large, so the local storage size is32GiBIt will be.
When you run the command, check the "FreeLocalStorage" metrics in CloudWatch.
We then found that the error occurred when the value got close to 0.
*Since ALTER was executed twice, this metric shows two instances of approaching 0.
 
When creating a secondary index with ALGORITHM=INPLACE for online DDL, a log file is created that records the DML that was performed during the DDL execution,
Temporary files such as sort files are created. These temporary files use local storage, and it seems that the query error occurred because the capacity was insufficient.
 

Solution

There are three possible ways to deal with this:
  • Increase the instance size to get more local storage
  • Specify the algorithm as ALGORITHM=COPY when executing ALTER TABLE
  • Use the RDS B/G Deployments feature to add an index with ALGORITHM=COPY only to the Green cluster
 

Increase the instance size to get more local storage

This time I dealt with it this way.
By increasing the size from db.r6g.large to db.r6g.xlarge, the local storage capacity80GiBIt has become.
 
When we ran the same ALTER command again in that environment, it was able to complete the operation without running out of local storage.
 

The algorithm for ALTER TABLE execution isALGORITHM=COPYDesignated as

Running ALGORITHM=COPY can help prevent local storage exhaustion.
However, since a table lock is required until execution is complete, update DML cannot be accepted at the same time.
I decided to postpone this this time because I needed to put the system into maintenance mode or some other way to make sure that other update queries were stopped without any problems.

Use the RDS B/G Deployments feature to deploy only to the Green cluster.ALGORITHM=COPYAdd INDEX

This method involves performing the above on a Green cluster created using the RDS Blue/Green Deployments function and then switching between B/G.
In this case, there is no data reference from the service in the Green cluster, so it is not a problem if the table is locked until execution is complete.
 
However, please note that there will be a downtime of about 1 minute when switching between B/G.
 

Conclusion

Online DDL may fail due to limitations in Aurora MySQL specifications.
It is necessary to be familiar with the limitations of local storage and learn to live with them.
 
SRG is looking for people to work with us. If you are interested, please contact us here.