CODEWITHSUNDEEP
mysqlgoogle-cloud-sql
Cymmer John
Cymmer John

Reputation: 255

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.

Upvotes: 0

Views: 980

Answers (2)

tarunratan
tarunratan

Reputation: 39

Hi Ive been getting the same issue and how i solved it was using the option of disabling point in recovery in my cloudsql As far as in my case the cloudsql went to 2TB before we could notice and I've taken a backup and restored it in a new Cloudsql commands i used to check if logging is causing the issue SHOW BINARY LOGS; once i got the logs we can delete them manually till the binaries we want as below PURGE BINARY LOGS TO 'mysql-bin.001298'; or simply follow the below steps which would delete all the logs edited the new instance and disabled the point in recovery option which made the issue solve below article as reference Google Cloud SQL increasing size until full disk with no reason

Upvotes: -1

Roopa M
Roopa M

Reputation: 3009

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

Upvotes: 1

Related Questions