Inaccurate table size calculation of Mysql

[hoptical]

At the moment, the below command is used in order to calculate the storage size of the table in MySQL:

sudo du -bcs /var/lib/mysql

Because of the irrelevant files of the Mysql (about 1G) and Binlog files (tens of Gigabytes), it doesn't seem reasonable to me to calculate table size in the mentioned way. Instead, it may be more appropriate to use the below query:

SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "test"
    AND table_name = "hits";

This will give us the actual size of the hits table. For 10M rows of the table, I got 14G for the first command and 6.4G for the proposed query. I found out that about 6G of 14G was due to the Binlog files, which are ephemeral, and I don't see any point to include them in the storage result.

Consequently, for the full hits table, we may end up with at most 70G instead of 160G, which is more rational to me.

What do you think?

[alexey-milovidov]

Yes, that will be better; let's fix it.