Help us improve the AWS re:Post Knowledge Center by sharing your feedback in a brief survey. Your input can influence how we create and update our content to better support your AWS journey.
impact of optimise table in rds
After running the OPTIMIZE TABLE command on the RDS master instance, the replica initially had enough storage. However, once the optimization finished on the master, the replica ran out of storage, and the application went down.
What’s strange is that the replica has the same storage size and instance type as the master, and our engine is MySQL. So why did the same behavior that occurred on the master not happen on the replica?
- Topics
- Database
- Language
- English
- Newest
- Most votes
- Most comments
When you run OPTIMIZE TABLE on an RDS MySQL master instance, it can have different impacts on the master and replica instances, even if they have identical configurations.
The OPTIMIZE TABLE command rebuilds tables to reclaim unused space and reorganize data, which initially creates temporary copies of the tables being optimized. This process requires additional storage during execution. On the master, the operation completed successfully because it had sufficient storage throughout the process.
However, on the replica, there's an important difference in how this operation is handled. When OPTIMIZE TABLE runs on the master, the actual SQL command is replicated to the replica as a binary log event. The replica then executes the same operation independently. This means the replica also needs to create temporary copies of the tables during optimization.
The likely reason your replica ran out of storage while the master didn't is due to differences in:
- Initial free storage space - the replica may have had less available storage before the operation started
- Binary logs or relay logs - the replica maintains additional logs that consume storage
- Temporary space usage - during replication, the replica might temporarily need more space to process the changes
To prevent this issue in the future:
- Monitor free storage space closely on both master and replica
- Enable storage autoscaling to automatically increase storage when it runs low
- Consider performing maintenance operations during low-traffic periods
- Ensure you have sufficient free space before running storage-intensive operations like OPTIMIZE TABLE
When a MySQL DB instance runs out of storage, Amazon RDS automatically stops it to prevent metadata inconsistencies and other issues, which explains why your application went down.
Sources
Known issues and limitations for Amazon RDS for MySQL - Amazon Relational Database Service
Resolve issues that occur when Amazon RDS runs out of storage | AWS re:Post
