Google Cloud SQL MySQL High Memory Usage After a Database Export

Did you do a DB export on your Cloud SQL instance and notice a big spike in memory usage? That can be normal during the export, but what if the memory doesn't come back down to where it was before the export?

That is exactly what happened to us with our MySQL Cloud SQL instance in GCP:

Our memory usage shot up to around 90% after an export and just stayed there. Naturally, since Cloud SQL is a managed service, we raised a ticket with Google Cloud Support.

When we explained the situation, they pointed out that a standard export caches data during the export process, and some of that data can remain in memory even after the export has completed.

In other words, even though the export itself has finished, some of the memory used during the operation can remain allocated to the MySQL InnoDB buffer pool.

So we ended up with something like this:

Before export: ~10% memory usage During/after export: ~90% memory usage Export completed: ...still ~90%

So what are our options to bring it back down? We obviously don't want to risk an OOM on our MySQL instance.

The answer from Google Cloud Support was to implement the following two flags on our MySQL instance:

innodb_cloudsql_managed_buffer_pool = on innodb_cloudsql_managed_buffer_pool_threshold_pct = 50

Simply Edit the Cloud SQL instance, scroll down to the Flags section, add the above flags with the threshold_pct value you want, and save the changes.

The first flag enables Google's managed buffer pool behavior, while the second defines the memory utilization threshold.

That's it.

This essentially places a memory threshold on the buffer pool, controlling how much memory it is allowed to utilize. The threshold value can be anything between 50 and 99.

We set ours to 50, and down came the memory that had been consumed during the export.

It didn't return all the way to our initial memory usage of around 10%. Instead, it dropped to approximately 49%, which makes sense since we had configured the threshold at 50%.

Google Cloud Support also recommended another approach:

Cloud SQL offload exports.

Instead of making your existing Cloud SQL instance handle the export workload, an offload export creates a temporary serverless instance to perform the export.

The main benefit is pretty straightforward:

Your database instance doesn't have to do all the heavy lifting.

This can help keep the CPU and memory utilization of your primary Cloud SQL instance under control while the export is running.

If you're regularly exporting large databases, this is definitely an option worth considering.

So, if you're running MySQL on Google Cloud SQL and notice that memory usage remains unusually high after a database export, have a look into the innodb_cloudsql_managed_buffer_pool settings.

#mysql #gcp #databases