Why is there a difference between reported and actual database storage usage?

Questions & Answers
, , , , , ,
#1

The database usage reported by platform db:size may differ from the actual database storage usage. To get a quick overview of the discrepancies and a quick fix, use platform db:size --cleanup. For more detail, please continue reading.

For example, MySQL/MariaDB pre-allocates storage for the tables and this storage is not immediately reclaimed if rows are removed from the table.

To review the database size and storage that can be freed by optimizing the tables:

MariaDB [(none)]> SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+----------------------+------------------+
| Data Base Name     | Data Base Size in MB | Free Space in MB |
+--------------------+----------------------+------------------+
| information_schema |           0.18750000 |       0.00000000 |
| main               |        3007.04687500 |     641.00000000 |
| mysql              |          11.36718750 |       4.00000000 |
| performance_schema |           0.00000000 |       0.00000000 |
+--------------------+----------------------+------------------+

To review the tables with the largest gain:

MariaDB [(none)]> SELECT table_name, round((data_length+index_length)/1048576,2) as size_MB, round((data_free)/1048576,2) as allocated_but_unused_MB FROM information_schema.tables WHERE 1 AND data_free > 1048576*10 ORDER BY data_free DESC;
+-------------------------------+---------+-----------------+
| table_name                    | size_MB | allocated_but_unused_MB |
+-------------------------------+---------+-------------------------+
| magento_logging_event_changes |    1.03 |                  109.00 |
| quote_item                    |  113.20 |                   93.00 |
| quote_item_option             |   61.09 |                   59.00 |
| avatax_log                    |  101.98 |                   14.00 |
| importexport_importdata       |    0.03 |                   11.00 |
| quote_address                 |   18.33 |                   11.00 |
+-------------------------------+---------+-------------------------+

To reclaim this storage, you can run the OPTIMIZE table​ command with the below warnings in mind:

  • OPTIMIZE can safely be done while online. There’s some potential performance impact depending on the table structure, though, so I would not recommend doing it during peak traffic unless absolutely necessary.

  • Be aware that OPTIMIZE makes a temporary copy of the table during the operation, so you need to have slightly more disk space than the table being optimized to be able to complete the operation. If there is not enough space to optimize, try starting from the smallest table and working up to the largest, that way you’ll free space as you go, and potentially have enough for the largest tables by the time the operation is completed.

Please note, that full-text search indexes are not calculated towards the database size:

root@mysql.0:/mnt/data/mysql/main# ls -lSh FTS*| head -10
-rw-rw---- 1 app app 804M Oct 29 09:22 FTS_0000000000009fe3_000000000001af88_INDEX_1.ibd
-rw-rw---- 1 app app 472M Oct 29 03:28 FTS_0000000000009fe3_DELETED.ibd
-rw-rw---- 1 app app 456M Oct 29 09:22 FTS_0000000000009fe3_000000000001af88_INDEX_2.ibd
-rw-rw---- 1 app app 356M Oct 29 09:22 FTS_0000000000009fe3_000000000001af88_INDEX_5.ibd
-rw-rw---- 1 app app 316M Oct 29 09:22 FTS_0000000000009fe3_000000000001af88_INDEX_4.ibd
-rw-rw---- 1 app app 268M Oct 29 09:22 FTS_0000000000009fe3_000000000001af88_INDEX_3.ibd
-rw-rw---- 1 app app 144M Oct 29 09:22 FTS_0000000000009fe3_000000000001af88_INDEX_6.ibd
-rw-rw---- 1 app app  23M Oct 29 09:22 FTS_0000000000004a5f_000000000000e5d3_INDEX_1.ibd
-rw-rw---- 1 app app  20M Oct 29 09:22 FTS_0000000000000a1b_0000000000001f1d_INDEX_1.ibd
-rw-rw---- 1 app app  17M Oct 29 09:22 FTS_0000000000000a33_0000000000001f50_INDEX_1.ibd

Also, please note that the database contains system files which cannot be removed:

root@mysql.0:/mnt/data/mysql# ls -al
total 194868
drwx------ 5 app app     4096 Oct 29 09:22 .
drwxr-xr-x 3 app app     4096 Dec  5  2019 ..
-rw-rw---- 1 app app   901120 Oct 29 09:47 aria_log.00000001
-rw-rw---- 1 app app       52 Oct 29 09:47 aria_log_control
-rw-rw---- 1 app app  1337093 Oct 29 09:22 ib_buffer_pool
-rw-rw---- 1 app app 52428800 Oct 29 10:29 ib_logfile0
-rw-rw---- 1 app app 52428800 Oct 29 09:57 ib_logfile1
-rw-rw---- 1 app app 79691776 Oct 29 10:29 ibdata1
-rw-rw---- 1 app app 12582912 Oct 29 09:22 ibtmp1
drwx------ 2 app app   126976 Oct 29 09:36 main
-rw-rw---- 1 app app        0 Dec  5  2019 multi-master.info
drwx------ 2 app app     4096 Oct 12 07:19 mysql
-rw-r--r-- 1 app app       16 Oct 12 07:19 mysql_upgrade_info
drwx------ 2 app app     4096 Oct 12 07:19 performance_schema
1 Like