Cloud SQL - Database Storage Usage Spike Due to Temporary Files

Google Cloud Database
1

Issue: A few days ago, our database storage usage increased by 3GB within a few hours. Upon reviewing System Insights, we noticed a spike in Temporary Files associated with a specific table. We managed to reduce the Temporary Files by disabling Point-in-Time Recovery (PITR), but this did not impact the overall Storage Usage. Some users on StackOverflow suggested restarting the SQL instance to address Storage Usage, but we have not attempted this, as we are unsure how long the instance restart would take.

What We’ve Tried:

  • Disabled Point-in-Time Recovery

What We’ve Achieved:

  • Reduction in Temporary Files

Goal:

  • Reduction in Storage Usage

Questions:

  1. Will the Temporary Files be automatically deleted after 7 days following the disabling of Point-in-Time Recovery?

  2. Is there a way to manually delete the Temporary Files?

  3. Will restarting the SQL instance remove the Temporary Files?

2
  1. No. PITR affects binary logs, not temporary files. Temporary files are managed separately and won’t be automatically deleted after disabling PITR.
  2. No. Temporary files are managed internally by the database engine and cannot be manually deleted. They may clear automatically after query completion or an instance restart.
  3. Yes, likely. Restarting the instance usually clears temporary files. However, storage space reclamation may not be immediate, and downtime (5–15 minutes) should be expected.

Here are some recommendations:

  1. Restart the Instance: Schedule a restart during maintenance to clear temporary files and reclaim storage.
  2. Optimize Queries: Identify and fix queries causing excessive temporary file usage (e.g., large ORDER BY, GROUP BY, joins).
  3. Increase Storage: If necessary, add storage or enable automatic storage increases to prevent outages.
  4. Monitor & Seek Support: Use query insights to track issues, and contact Google Cloud Support for persistent problems.