AWS RDS MySQL difference between free_data and Free Storage Space metric-CodePudding

I'm struggling to find what consuming storage on MySQL RDS instance.

We have 30Gb RDS instance

Slow and general logs are disabled;

binlogs removed immediately (better say as soon as possible)

Free Storage Space metric reports ~10Gb.

Trying to calculate used storage with next query (see output below):

SELECT table_schema, SUM(data_length index_length data_free)/1024/1024/1024 AS total_Gb, SUM(data_length)/1024/1024/1024 AS data_Gb, SUM(index_length)/1024/1024/1024 AS index_Gb, SUM(data_free)/1024/1024/1024 AS free_Gb, COUNT(*) AS tables FROM information_schema.tables GROUP BY table_schema ORDER BY 2 DESC;

Since the total size of the database (including free_data for this database) is 13.6Gb, I don't understand where the rest of the storage is missing (5-6Gb).

Maybe someone has any ideas why this storage is not taken into account on metrics?

| table_schema       | total_Gb        | data_Gb         | index_Gb       | free_Gb         | tables |
 -------------------- ----------------- ----------------- ---------------- ----------------- -------- 
| information_schema | 14.156433105469 |  0.000183105469 | 0.000000000000 | 14.156250000000 |     63 |
| main_database      | 13.608055360615 | 11.013053961098 | 1.915313899517 |  0.679687500000 |    373 |
| mysql              |  0.018694377504 |  0.008742786013 | 0.000185966492 |  0.009765625000 |     43 |
| sys                |  0.000015258789 |  0.000015258789 | 0.000000000000 |  0.000000000000 |    101 |
| performance_schema |  0.000000000000 |  0.000000000000 | 0.000000000000 |  0.000000000000 |     87 |
 -------------------- ----------------- ----------------- ---------------- ----------------- -------- ```

CodePudding user response：

With this query, you would have more details to identify table using the 14 G in information_schema.

SELECT table_schema, table_name, SUM(data_length index_length data_free)/1024/1024/1024 AS total_Gb, SUM(data_length)/1024/1024/1024 AS data_Gb, SUM(index_length)/1024/1024/1024 AS index_Gb, SUM(data_free)/1024/1024/1024 AS free_Gb, COUNT(*) AS tables FROM information_schema.tables GROUP BY table_schema, table_name ORDER BY 3 DESC;

What is result of SELECT @@version; ?

CodePudding user response：

Run a similar query, but look at each table in your database. You will probably find that each table has between 4M and 7M of "Data_free". This is normal. The database preallocates that space.

If you find a table that has more than 7MB free, then let's look more closely at it.

Also, do you have hundreds of tables? Or lots of PARTITIONs in some tables? Again, we can discuss the details once you have presented them.