Google CloudSQL instance storage is growing out of control

Question

I have encountered an issue with Ggoole Cloud SQL (2nd gen). For some reason after a while, the database went from 20GB to 64GB in a matter of hours. It used to climb from 20 to 25 then purge as entries were added and removed over time.

Nothing happened on the server connecting to the database, and I have Cloud SQL flags set to off. Any ideas what else I can try?

Is binary logging enabled? https://cloud.google.com/sql/docs/mysql/backup-recovery/restoring#enablingpitr — LundinCast, Nov 25 '18 at 11:49
@LundinCast, yes it is. The database itself is 1.4 GB, no major changes made. Could binary logging cause it to blow up by 400% like this? Looking at the history there, it was increasing increasing then purging and so on. I had that same pattern with a steady increase for over a year on this DB with no issues. — Adrian, Nov 25 '18 at 21:39

score 4 · Accepted Answer · answered Nov 26 '18 at 10:44

This is most likely due to binary logs. When they are enabled, MySQL will make a record of all changes, which is required for replication or point-in-time recovery. This means that the growth of binary logs is roughly proportional to the amount of modified rows (even if these rows were actually deleted and db total size reduced).

Note that they will not grow indefinitely. Binary logs older than the oldest automatic backup (7 days) are purged automatically.

Also note that storage size can be increased (I believe you have automatic storage increase enabled) but it cannot be decreased, as documented here. This means that when binary logs are purged, free disk space will increase but the total storage size will remain identical. If you want to reduce your disk size after binlogs are purged, you can follow the suggested method here.

with binary logging, the usage never went down, and I have backups every morning. After turning them off however, the size went back down to where it was before. As a solution to my particular issue, I moved the database (1.4 GB) to a new server where I made sure binary logging was off from the start. The size of that server now hovers around 4-5 GB. The original question remains however: With the connected server not showing any irregular activities meaning not a lot of changes to the DB, how would binary logs get this out of control all on their own? Could something else be involved? — Adrian, Nov 26 '18 at 14:25

score 0 · Answer 2 · answered Sep 28 '20 at 17:47

Here is some Google Cloud docs on this matter: https://cloud.google.com/sql/docs/mysql/replication#bin-log-impact

And, here is some interesting mysql commands to see & purge them:

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 | 106930110 |
| mysql-bin.000002 | 102842758 |
| mysql-bin.000003 | 109947365 |
....

https://dev.mysql.com/doc/refman/5.7/en/show-binary-logs.html

mysql> PURGE BINARY LOGS BEFORE '2020-10-28 00:00:00';

https://dev.mysql.com/doc/refman/5.7/en/purge-binary-logs.html

score 0 · Answer 3 · answered Nov 24 '22 at 10:00

We experienced a similar problem and was not the binary logs. Doing maintenance resolve the problem, but it's not the root cause.

The problem was a sort on a bad query!

If you make an "explain" of a query and you see "Using temporary; Using filesort" this means that MySQL create a temp file.

This file could be huge (especially if the query make an outer join!)

Google CloudSQL instance storage is growing out of control

3 Answers3