history_uint.ibd Size Issue

[abdalazizjanem]

Dears,

Please your advise how to reduce the size of the file "history_uint.ibd" ?
mysql mysql 30G Jun 26 09:45 history_uint.ibd

taking into your consederation the DB size is less than 5G as bellow:
+---------------------+---------------+-------------+
| Database Name | Size in Bytes | Size in MiB |
+---------------------+---------------+-------------+
| zabbix | 4324212736 | 4123.89 |
+---------------------+---------------+-------------+

how this happened?

Regards,

[Markku]

If I understand this correctly, your database files have lots of deleted data and the space is not reclaimed automatically. Check your database documentation for ways to reclaim the space ("vacuum" may or may not be a keyword for that).

One way to do that is to dump the database to a file and then import it to a new database. You can use the same server if you like. The reconfigure Zabbix server and the frontend to use the new database. When successful, you can delete the old database. Make sure to have appropriate backups before doing anything.

Markku

[tim.mooney]

PostgreSQL's vacuum/auto-vacuum seems pretty nice, but I know less about pgsql than MySQL/MariaDB/Percona. With any of those, to rebuild a table and make it temporarily less sparse, you have to issue the "OPTIMIZE TABLE <tablename>" command. It uses temporary space on disk up to something like (from memory) 150% of the existing size of the table you are optimizing.

Or dump & reload, as you said.

[abdalazizjanem]

Thanks tim.mooney, this solved my issue!

[Markku]

You may want to also check this:

https://blog.zabbix.com/partitioning...cedures/13531/

Markku

[tim.mooney]

Dears,

Please your advise how to reduce the size of the file "history_uint.ibd" ?
mysql mysql 30G Jun 26 09:45 history_uint.ibd

taking into your consederation the DB size is less than 5G as bellow:
+---------------------+---------------+-------------+
| Database Name | Size in Bytes | Size in MiB |
+---------------------+---------------+-------------+
| zabbix | 4324212736 | 4123.89 |
+---------------------+---------------+-------------+

how this happened?

If you just used "ls -l" to list file sizes, that's showing you the apparent file size, not how many blocks are actually being utilized.

You need to understand that Linux and Unix before it have a long history of supporting what are called "sparse files". Check the wikipedia entry for more info: https://en.wikipedia.org/wiki/Sparse_file

As long as you're not close to filling that filesystem, your sparse history_unit.ibd is not doing any harm. You can safely ignore that it looks like a big file, but it's mostly empty inside.

If it really concerns you, you can use the MySQL "OPTMIZE TABLE" command to effectively rebuild the file and get rid of the empty space. See: https://dev.mysql.com/doc/refman/8.0...ize-table.html

Keep in mind that databases make intentional use of sparse files, so over time that file is going to become sparse again. If your history growth is relatively stable, it may not get as large in apparent size after being rebuilt, but it depends upon a number of factors.

You have other sparse files on your system too, beyond the database. That's perfectly normal on a Linux system.

[abdalazizjanem]

OPTMIZE TABLE Solved my issue! Thank you.
and thanks for other information you provided.

[Markku]

Sparse files, yeah, slightly tangential story but I'll tell it anyway

Just the other day I got an alert from Zabbix that disk I/O on a server was constantly high, caused by an hourly incremental backup run. I was worried because it shouldn't take more that a few seconds to run. What's going on, how were so many files suddenly changed, or is the RAID failing.

I quickly pointed the problem to /var/log/lastlog that was suddenly a 448 GB file! Small search around revealed the keywords "sparse file", and the root cause was that I had just enabled LDAP login authentication on the server, and it generates uids like in tens of millions (instead of the usual 1000 range). The file is indexed based on uids, so large uid values cause "large" (but sparse) lastlog file.

I excluded the file from the backup and all is good.

Markku

[abdalazizjanem]

Thank you, Markku, for this story, appreicated.