We have tried and failed four times to perform a Blue/Green RDS Aurora MySQL v5.7 to v8 upgrade in production, evidently due to excessive memory consumption... any help would be greatly appreciated!

Question

We have three environments: development, staging, and production. We worked out some kinks with the MySQL 5.7 to 8.0 upgrade in development and were able to use the Blue/Green deployment successfully. Our staging environment upgraded flawlessly using the Blue/Green on the first try. We were prepared for the production upgrade to take a while longer due to the size of the database, but based on experiences in the lower environments, thought it would go relatively smoothly. That has not been the case, unfortunately. Three days and four attempts later, we are stuck.

For reference our PROD writer instance class is db.r5.4xlarge. The first attempt failed, but the status of the greens never changed from Provisioning/Upgrading. In the event log we saw:

> The database process was killed by the OS due to excessive memory consumption. It is recommended to tune your database workload and/or parameter usage to reduce memory consumption.
and 
> Database could not be started due to incompatible parameters. It is recommended to tune your parameter to reduce memory consumption.

We had run through some preparatory steps on the first attempt to identify invalid database views, and we did notice certain views taking a long time to be instantiated, so we suspected perhaps the instance was running out of memory during the online pre-upgrade checks due to those views. Metrics showed the green instance CPU pegged very near to 100% for 10 minutes or so before abruptly dropping to near zero. At the same time, freeable memory on the instance dropped to zero. We had attempted to spin up the blue/green in the middle of the night, when application usage is low but we do a lot of jobs/ETL work that require a lot of data replication.

So for our second attempt, we removed some of the heavy views we noted during the first attempt, and we started the blue/green deployment in the middle of the business day. This time the upgrade seemed to go a little better, but the end result was the same.

For the third attempt during the next business day, we dropped even more non-performant database views that were slow to load, and we seemed to get farther along... rather than spiking CPU up to 100% for a few minutes and then crashing, we saw a slower rise to close to 100% CPU over the first hour, and then an abrupt drop to under 10%. But then CPU climbed again to nearly 100% for close to another hour before crashing to zero. Another failure.

So for the fourth attempt we thought perhaps boosting the instance size on the PROD writer from db.r5.4xlarge to db.r5.8xlarge might give us enough memory to work with. Nope. We had the stady climb to 100% CPU over 40 mins or so, and then it was pegged at 100% for another 30 mins before crashing to zero. There was no second wind this time.

So on all four attempts we have ended up stuck at the following step: Upgrade in progress: Performing online pre-upgrade checks. I'm not exactly sure where to go next. It certainly sounds like modifying parameters might be the answer:

> It is recommended to tune your parameter to reduce memory consumption.

But if anyone has recommendations about which parameters might be most impactful, we'd really appreciate some advice.

Giovanni Lauria · Answer

It sounds like you're encountering issues with memory consumption during the MySQL upgrade process in your production environment.

You may follow the followin plan to understand the real issue:
* Increase Instance Size: You've already attempted to increase the instance size to db.r5.8xlarge, but if memory consumption is still an issue, consider further increasing the instance size or using a larger instance type with more mmory to handle the upgrade process.
* Optimize Database Configuration: Review and optimize your MySQL configuration parameters to reduce memory consumption. Focus on parameters related to buffer sizes, cache sizes, and thread settings. Key parameters to consider tuning include innodb_buffer_pool_size, innodb_log_file_size, max_connections, and query_cache_size.
* Review Database Workload: Analyze the database workload during the upgrade process to identify any queries or operations that may be causing excessive memory consumption. Optimize or tune these queries if possible to reduce their memory footprint.
* Monitor System Resources: Continuously monitor system resources during the upgrade process to identify any spikes or patterns in CPU and memory usage. This can help pinpoint specific stages or operations that are contributing to the memory consumption.
* Parallelize Upgrade Steps: Break down the upgrade process into smaller, parallelizable steps to reduce the overall memory footprint at any given time. For example, you could perform schema upgrades and data migrations in separate stages to minimize memory usage during each step.
* Consider Maintenance Window: Schedule the upgrade process during a maintenance window when system usage is expected to be low, minimizing the impact on ongoing operations and resource contention.

Engage Database Experts: If you're still experiencing difficulties, consider engaging database experts or consultants who can provide specialized guidance and assistance with optimizing your MySQL configuration and upgrade process.

We have tried and failed four times to perform a Blue/Green RDS Aurora MySQL v5.7 to v8 upgrade in production, evidently due to excessive memory consumption... any help would be greatly appreciated!

Relevant content