Calculating MySQL database size

[Markku]

This is how the official MySQL template calculates the database size (in this example I only use history_uint table but the principle is valid, lines split for clarity):

# echo '
select sum(data_length+index_length)/1024/1024/1024 as "Size in GB"
from information_schema.tables
where table_schema="zabbix" and table_name="history_uint";
' | mysql

Size in GB
1.454421997070

while the filesystem says:

# du -sch history_uint*
4.0K history_uint.frm
4.0K history_uint.par
2.3G history_uint#P#p2022_02_27.ibd
2.3G history_uint#P#p2022_02_28.ibd
2.3G history_uint#P#p2022_03_01.ibd
2.2G history_uint#P#p2022_03_02.ibd
2.2G history_uint#P#p2022_03_03.ibd
2.3G history_uint#P#p2022_03_04.ibd
2.2G history_uint#P#p2022_03_05.ibd
2.3G history_uint#P#p2022_03_06.ibd
2.3G history_uint#P#p2022_03_07.ibd
2.3G history_uint#P#p2022_03_08.ibd
1.6G history_uint#P#p2022_03_09.ibd
112K history_uint#P#p2022_03_10.ibd
24G total

There is no housekeeper deleting history data from the database.

For the whole database the difference is this:

# echo 'select sum(data_length+index_length)/1024/1024/1024 as "Size in GB" from information_schema.tables where table_schema="zabbix";' | mysql
Size in GB
16.528808593750

# du -sch /var/lib/mysql/zabbix
50G /var/lib/mysql/zabbix
50G total

Can anyone explain this difference?

What would be the correct way to calculate the size of the MySQL (or in this case, MariaDB 10.5.12) database?

I'm asking because until one day in February the MySQL template reported numbers just fine, but after that the reported size was just getting smaller and smaller:



The partitioning task runs at midnight and keeps 10 days of partitions, so every night it purged the old history partitions and on 1st of March it purged the old trends data. Today I rebooted the database server, and now the reported size is growing again, let's see what happens tonight.

Markku

[Markku]

Still doesn't make any sense after yesterday:



So the select query says that the database size was increased by 1.5 GB by just rebooting the MariaDB server, and while partitioning maintenance task deleted all older history partitions at midnight, the database size still continued to grow (unlike what happened prior to Feb 10th). And actually the growth rate changed when the partitions had been dropped.

This is my first database in MariaDB 10.5.12, I wonder if it has some issues when calculating the size from the information_schema.tables data.

Markku

[Markku]

Same trend continues: disk space is freed at midnight due to the partition maintenance dropping old data, but the sizes reported in information_schema.tables keeps only growing.

For me it looks like this MariaDB 10.5.12 has problems with calculating the table sizes correctly. I'll try to remember update here if I get any further information on this.

Markku

[Markku]

No other further information but the reported table sizes have gradually returned to normal after the MariaDB restart in March.



Markku