GCP SQL Cloud - Empty MySQL Database taking 1.2GB of storage space

2
votes

I'm just looking for a clarification as the documentation state that an empty, just created, SQL Instance should only take 250Mb approximately.

Quoting from documentation about anything I could find on storage space:

MySQL Second Generation instances: The most recent 7 automated backups, and all on-demand backups, are retained. They are charged at the backup storage rate. Binary logs use storage space (not backup space), and are charged as storage.

For the purpose of this test, binary logging is disabled.

MySQL Second Generation [...]: Storage is calculated based on the amount of storage you have provisioned for your instance. Storage for backups is charged by how much space your backups are using. Storage is charged whether your instance is on or off.

Again, freshly created instance. It should be 0 storage space.

A newly created database uses about 270MB of space for system tables and InnoDB logs. If you create a per-use instance but do not use it, you are still charged for the storage cost.

This is where I got the idea about "250 MB" as initial storage space.
As you can see however, a newly created database takes around 1.2GB.

enter image description here

I'd like some clarification on it if someone has any.

Sources:

1
mysqlgoogle-cloud-platform
i assume you use the InnoDB engine.. MySQL InnoDB not releasing disk space after deleting data rows from table – Raymond Nijland
but above requires you to needs to reconfigure a MySQL setting (innodb_file_per_table), can you do that as you are within a cloud service? – Raymond Nijland
1) Why does this matter as in what problem are you trying to solve? 2) MySQL creates a set of log files (ib_logfile0, ib_logfile1, ibdata1, ibtmp1, ...) These are required files and they take up space. On my system, almost 200 MB. This Stackoverflow answer can help you understand these files: dba.stackexchange.com/questions/27083/… – John Hanley
@JohnHanley I'm simply trying to understand why this happens as every allocated space is paid for. If that space is a fixed amount, it wouldn't matter but if that increase (directly? exponentially?) it can cause additional costs with zero benefits. Trying to understand why it happens / what it is can help understand if it can be a problem and how to keep it under control. – Claudio
@RaymondNijland I can and I did but it didn't solve the issue as we didn't just "remove records" we deleted entire tables. That should trigger a clean up from the innoDB engine. The flag was turned on anyway so it is not a problem related to that issue. – Claudio

1 Answers

2
votes

I've been looking into this and the thing that you should take into account is that, the information you quoted about Cloud SQL MySQL empty instances occupying around 270MB is for first generation instances, not for second generation ones. I think that answers your question.

At first I interpreted that the same way as you did, but the only points where 270MB empty instances are specified are here and here which are within the "MySQL First Generation Pricing" category on the right.

Hope this helps.