Cloud SQL instance has massively inflated disk usage

[Kristian Freed]

I have a 2nd generation MySql 5.6 cloud SQL instance running with binlog enabled and a failover replica.

This has been in use steady state for some time, but recently I've noticed that the reported disk usage has gone up significantly. We have auto-increase enabled, but currently the disk usage reported in the cloud console is 8TB(!), whereas other tools, including the chart for "storage used" in cloud console report a much more sane 55GB.

I've looked at binlogs and they're all manageable size.

Is there any other reasons why the disk usage would be so massively inflated, and is there any way (other than re-creating the db from scratch) to force cloud sql to shrink it back down?

Cheers,

Kristian

[Pavel Ivanov]

Do you have general log enabled on the instance by any chance?

Pavel

> --
> You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/957d4155-6423-42f9-a74d-52f0d37958ea%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Kristian Freed]

We have not turned on any flags other than event_scheduler, so we're using the default value for that, which I believe should be off.

Cheers,

Kristian

On Wednesday, 15 May 2019 06:54:42 UTC+1, Pavel Ivanov wrote:

Do you have general log enabled on the instance by any chance?

Pavel

On Tue, May 7, 2019 at 10:24 AM Kristian Freed <kristi...@gmail.com> wrote:
>
> I have a 2nd generation MySql 5.6 cloud SQL instance running with binlog enabled and a failover replica.
>
> This has been in use steady state for some time, but recently I've noticed that the reported disk usage has gone up significantly. We have auto-increase enabled, but currently the disk usage reported in the cloud console is 8TB(!), whereas other tools, including the chart for "storage used" in cloud console report a much more sane 55GB.
>
> I've looked at binlogs and they're all manageable size.
>
> Is there any other reasons why the disk usage would be so massively inflated, and is there any way (other than re-creating the db from scratch) to force cloud sql to shrink it back down?
>
> Cheers,
> Kristian
>
> --
> You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.

> To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

[Sam (Google Cloud Support)]

Hey Kristian,

You can view current values of database flags by issuing 'SHOW VARIABLES;' statement once logged into your instance with the mysql client to view all current values of the MySQL system variables [1]. What is the value of the 'general_log' flag [2]? If it's enabled, that could mean that it is contributing to the disk usage.

Aside from that, it's very important to note that if you use on-demand backup (create a backup at any time) this could impact your disk usage and billing charges in the long-term. Because you can create on-demand backups for any Second Generation instance, whether the instance has automatic backups enabled or not, on-demand backups are not automatically deleted the way automated backups are. They persist until you delete them or until their instance is deleted. 

That being said, as they are not automatically deleted, on-demand backups can have a long-term effect on storage and your billing charges if you do not delete them. Could this be what you are noticing? More on on-demand backups here [3].

Hope this helps decipher what's going on with disk usage. If this doesn't quite address your issue, then providing some screenshots from the tools you are using and from the Cloud Console and we can investigate further, and if this is an issue with the instance then we can create a Public Issue Tracker for further troubleshooting using the details your provide.

Thank you! 

[1] https://cloud.google.com/sql/docs/mysql/flags#viewing_current_values_of_database_flags

[2] https://cloud.google.com/sql/docs/mysql/flags#mysql-g

[3] https://cloud.google.com/sql/docs/mysql/backup-recovery/backups#about_on-demand_backups

[Kristian Freed]

Hi,

Thank you for your reply. We did have a couple of on demand backups and I have now deleted most of them but with no impact on the disk usage reporting. The number I'm looking at is "SSD storage" in the instance details view in the Google Cloud Console, as can be seen in https://drive.google.com/file/d/1f9sQrqhM3_5QXPkL1z8VDP5_s1SluhBn/view?usp=sharing

Would this number include backups?

I have confirmed that general_log is off when using show variables.

Any other pointers would be much appreciated as we're now paying for the very inflated number every month and I cannot see a reason why.

Cheers,

Kristian

[Harmit Rishi (Cloud Platform Support)]

Hi, 

As the documentation provided above does not help decipher whats going on with the disk usage for your use case of a Cloud SQL instance, I believe your issue requires some further investigation by Support. Evaluating your specific instance settings on our end will allow us to get a better understanding of what setting may be bloating your disk.

Due to this, you may feel free to create a private issue on our Public Issue Tracker. Setting the issue tracker component to private will ensure that your Cloud SQL instance information would not be shared publicly. 

Looking forward to assisting you!