AWS RDS MySQL difference between free_data and Free Storage Space metric

user_9658170 0
Viewed 106

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).

The main question is why FreeStorageSpace metric in CloudWatch shows only 10Gb free while I can see almost 15Gb free on MySQL?

| 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 |
+--------------------+-----------------+-----------------+----------------+-----------------+--------+```

Here is allocation for information_schema tables:

| table_schema       | table_name                                              | total_Gb       | data_Gb        | index_Gb       | free_Gb        | tables |
+--------------------+---------------------------------------------------------+----------------+----------------+----------------+----------------+--------+
| information_schema | PROCESSLIST                                             | 1.179702758789 | 0.000015258789 | 0.000000000000 | 1.179687500000 |      1 |
| information_schema | EVENTS                                                  | 1.179702758789 | 0.000015258789 | 0.000000000000 | 1.179687500000 |      1 |
| information_schema | PLUGINS                                                 | 1.179702758789 | 0.000015258789 | 0.000000000000 | 1.179687500000 |      1 |
| information_schema | VIEWS                                                   | 1.179702758789 | 0.000015258789 | 0.000000000000 | 1.179687500000 |      1 |
| information_schema | PARTITIONS                                              | 1.179702758789 | 0.000015258789 | 0.000000000000 | 1.179687500000 |      1 |
| information_schema | RDS_EVENTS_THREADS_WAITS_CURRENT                        | 1.179702758789 | 0.000015258789 | 0.000000000000 | 1.179687500000 |      1 |
| information_schema | RDS_PROCESSLIST                                         | 1.179702758789 | 0.000015258789 | 0.000000000000 | 1.179687500000 |      1 |
| information_schema | PARAMETERS                                              | 1.179702758789 | 0.000015258789 | 0.000000000000 | 1.179687500000 |      1 |
| information_schema | TRIGGERS                                                | 1.179702758789 | 0.000015258789 | 0.000000000000 | 1.179687500000 |      1 |
| information_schema | COLUMNS                                                 | 1.179702758789 | 0.000015258789 | 0.000000000000 | 1.179687500000 |      1 |
| information_schema | OPTIMIZER_TRACE                                         | 1.179702758789 | 0.000015258789 | 0.000000000000 | 1.179687500000 |      1 |
| information_schema | ROUTINES                                                | 1.179702758789 | 0.000015258789 | 0.000000000000 | 1.179687500000 |      1 |
amazon-web-services mysql
2 Answers

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; ?

user_44206490

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.

More

I suspect that "Free Storage Space" refers to disk space in general, and has nothing to do with the database. The SELECT refers to disk space allocated to the database, but not currently in use. Those two quantities are mostly unrelated.

If you get a bigger disk (move to a different RDS offering), the disk amount will increase; the SELECT will not change. If you add lots of rows to your database tables, the disk amount will decrease and the SELECT will show an increase in Data_length and Index_length, but Data_free will not change significantly and may go up or down.

user_17668310
Related