Hello all.
We have a customer that uses MySQL 8.0 with Cloud SQL. They brought us an interesting question. They ran this query, to try to reckon the amount of data on the database
mysql> SELECT table_schema AS 'Schema',ROUND(SUM(data_length) / (1024 * 1024 * 1024), 2) AS 'Data (GB)',ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2) AS 'Index (GB)',ROUND(SUM(data_free) / (1024 * 1024 * 1024), 2) AS 'Free/Fragmented (GB)',ROUND((SUM(data_length) + SUM(index_length) + SUM(data_free)) / (1024 * 1024 * 1024), 2) AS 'Total Estimated Disk Usage (GB)' FROM information_schema.tables WHERE table_type = 'BASE TABLE' GROUP BY table_schema;
+--------------------+-----------+------------+----------------------+---------------------------------+
| Schema | Data (GB) | Index (GB) | Free/Fragmented (GB) | Total Estimated Disk Usage (GB) |
+--------------------+-----------+------------+----------------------+---------------------------------+
| lportal | 124.88 | 76.10 | 2.82 | 203.81 |
| performance_schema | 0.00 | 0.00 | 0.00 | 0.00 |
+--------------------+-----------+------------+----------------------+---------------------------------+
2 rows in set (3.58 sec)
Nonetheless, the “Storage usage” shows that the service is using more than 240 GB, which is above the ~200 GB the query gave us.
I imagine there is a lot of extra data necessary to keep the database up and running, but a difference of almost 40 GB is considerable.
What could be causing the difference? I have checked everything (bin log, audit log, undo log etc.) and none seems to affect it.
Thanks in advance!