Google Cloud MySQL storage space skyrocketed due to tmp_data

[marcogalassi]

Our Google Cloud MySQL database had a huge and unexpected increase of used space in the span of a couple of days. It went from 15 GB to 16TB (!!!). This is also obviously causing issues with our billing as the cost for the SQL storage drastically increased.

After checking the storage via the Google Cloud SQL System Insights, we noticed that the increase is due to what Google SQL calls `tmp_data`. Here you can see how it skyrocketed:

Restarting the database instance cleared this `tmp_data` and restored the DB storage usage to the expected 15 GB it was before.

The SQL logs also don't seem to show anything significant other that error messages related to no available storage when the limit was hit.

I would like to understand what might be causing this issue. To start, I tried to look up what `tmp_data` is, but I cannot find a reliable answer to what this represents.

Questions:

- What is `tmp_data` in Google Cloud MySQL?
- What could I investigate to try to understand what the issue might be?

[ms4446]

tmp_data is temporary data that created by the MySQL server during operations like sorting, joining, or handling large queries that require more memory than the server has available. When these operations can't be completed in memory, MySQL spills over to disk, generating temporary files that are stored in a directory known as tmpdir. Ideally, these files should be cleared when no longer needed, but if they accumulate or are not properly removed, they can cause a significant increase in storage usage.

Several factors could lead to this issue. Large or complex queries that involve sorting, joining large tables, or creating temporary tables can generate large amounts of temporary data. Additionally, queries that are not optimized may result in excessive use of temporary tables, contributing to the problem. Another potential cause is InnoDB purge lag, where the delay in purging old data leads to an accumulation of undo logs in the temporary space, further increasing tmp_data. Insufficient memory allocation to MySQL is also a common cause, as it forces the database to use disk space for operations more frequently.

To diagnose and address this issue, it is important to investigate several areas. Reviewing query performance and execution plans can reveal whether complex queries are creating large temporary tables. Monitoring the number of temporary tables created can help identify if disk-based temporary tables are being used excessively. Additionally, reviewing the instance's memory settings ensures that MySQL has enough memory to handle operations without resorting to disk usage. Checking the InnoDB purge status and inspecting MySQL logs for unusual activity can also provide insights into the underlying cause.

In your case, restarting the instance cleared the temporary data and restored the expected storage usage. However, identifying and addressing the root cause is important to prevent the issue from recurring and causing further disruptions in storage usage and billing.

[marcogalassi]

This indeed looks like the issue. We have also switched the MySQL internal_tmp_disk_storage_engine from InnoDB to MyISAM, but unfortunately this doesn't solve the problem