Search code examples
mysqlgoogle-cloud-sql

CloudSQL Storage Usage Issue

I have a database in GCP's CloudSQL with these configurations. enter image description here

Storage usage has increased exponentially in recent weeks. In just about 1 month, the storage usage increased from about 2 TB to 3 TB. It's just an anomaly in our case since, way before, our storage usage did not increase that drastically (e.g., just MBs instead of GBs). 

I've also queried the total storage usage in the database using this command.

SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
FROM information_schema.TABLES 
GROUP BY table_schema;

And this was the output: enter image description here

I have tried researching online about this issue, and it seems that it's not just me who has experienced it.

I looked at these StackOverflow issues already:

  1. Google Cloud SQL Storage Usage High Issue
  2. Google Cloud SQL increasing size until full disk with no reason

So I thought that it's because maybe I have enabled the point-in-time recovery setting which uses binary logging. So,I already disabled that setting for the past two weeks (as seen in the first image). However, the storage allocation still hasn't gone down, and is still increasing at the moment.

Is there another reason why it hasn't been reduced yet? 

Edit: Some solutions say that I just need to make a SQL dump, make another instance, and import this dump. However, I am dealing with a production database with hundreds of users, and the estimated downtime would be around 2–3 days from exporting to importing the dump. I would not want to go with this workaround unless there's really no other option.

Solution

  • This might be due to ‘Temp data’ , such as data used by queries using temporary tables or sorting. They get accumulated due to inefficient queries being run on the instance.

    You can try using commands like \dt to view temporary tables that might be using the space. You can identify the queries responsible for this by running this command show processlist;

    Restarting the instance will delete the ‘Temp data’. But, it will not reduce the disk size. To reduce the size you have to Contact google support

    If the above solution does not work, Try the troubleshooting steps from this documentation