I am a beginner and struggling with figuring out increasing storage size on MySQL RDS.
The issue is that I want to increase RDS storage size without any downtime. Let's assume that I have not enabled autoscaling on RDS by default. I researched this and found that there are fundamentally two options:
Option 1) Enable Auto Scaling. AWS documentation is at https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIOPS.StorageTypes.html. However, the official documentation doesn't specify whether there will be no downtime. It says that I can turn on/off autoscaling without talking about downtime.
Option 2) Provision more space directly The official documentation also says that I can provision more storage size with the caveat that
In most cases, scaling storage doesn't require any outage and doesn't degrade performance of the server.
From SO thread (https://serverfault.com/questions/613182/downtime-for-increasing-aws-rds-storage), it seems this process doesn't seem to be seamless.
Option 3) Back-up and restore: I would back-up RDS. Then, I'd increase the storage capacity. Then, I'd restore RDS and stop the previous instance.
Question: Can someone please guide me which option will have no downtime and will create relatively low amount of headache for us?
My hypothesis is that Option 1) will the best, but I just want to confirm my hypothesis with experts.
From reading that documentation, Auto-scaling of storage triggers a "scale-up" of storage that is the same as "Increasing DB instance storage capacity" directly. It also has the benefit that it will automatically scale in future.
There is a special-case mentioned for SQL Server instances that have been running since 2017, but it would appear from reading the documentation that all other db instances would not have an outage.
Option #3 would definitely have an outage, so that would not be a wise option.
I would recommend taking a manual backup, then activating Auto Scaling during a quiet period (eg at night).
You can always experiment by creating another database and triggering a scale-up of storage, to see what happens.