Zabbix db grows 1gb/day

[Grrruk]

Recently I began to replace my own primitive decade-old monitoring scripts with Zabbix. Well, after a few days of joy I discovered that Zabbix Grows. I dumped the database, deleted old files from /var/lib/mysql, changed mysqld section in /etc/mysql/my.cnf to innodb_file_per_table and recreated db from .sql dump. Well, now instead of ibdata1 growing 1gb/day I have history_uint.ibd doing the same... Also, I ran optimization scripts and tried to tweak mysql configuration. It did not help too.

Now, what to do next? How to find which host and which item consumes too much of disk space? Something like du *|sort -rn in unix?

BTW, today dump of database takes only 2.9 gb (as single .sql file) instead of 9.3 gb of everything in /var/lib/mysql. Is that normal?

My configuration is not that big...

Zabbix server is running Yes 10.1.0.25:10051
Number of hosts (monitored/not monitored/templates) 63 14 / 0 / 49
Number of items (monitored/disabled/not supported) 1504 1457 / 0 / 47
Number of triggers (enabled/disabled) [problem/ok] 492 485 / 7 [0 / 485]
Number of users (online) 4 1
Required server performance, new values per second 391.33 -

[ingus.vilnis]

Hello and welcome to Zabbix!

Here will be some quick pointers from my side:

• You have really big number of new values per second. It is normal that your DB will grow because of that. Therefore please check and increase the Update interval for your items that does not need to be updated every second.
• To find out such items you have to open item list for any host and then use the filters to check the update intervals.
• To change them more quickly look for Mass update feature https://www.zabbix.com/documentation...ems/itemupdate
• Consider using Housekeeper as described here: https://www.zabbix.com/documentation...al#housekeeper

Best Regards,
Ingus

[Grrruk]

Hello and welcome to Zabbix!

Here will be some quick pointers from my side:

• You have really big number of new values per second. It is normal that your DB will grow because of that. Therefore please check and increase the Update interval for your items that does not need to be updated every second.
• To find out such items you have to open item list for any host and then use the filters to check the update intervals.
• To change them more quickly look for Mass update feature https://www.zabbix.com/documentation...ems/itemupdate
• Consider using Housekeeper as described here: https://www.zabbix.com/documentation...al#housekeeper

Best Regards,
Ingus

Thank you very much, I found at least one wrong thing -- some Mikrotik template used discovery rules to create a lot of items with 1 second update! Heh. I fixed that. But it seems I can't erase old data, I tried to remove and unlink that template, but it did not cause database to became any smaller. I tried to dump and restore again, but nothing changed. Tomorrow I'll see if it grows again or not.

Uhhh, I wish there was a pie chart that shows how much data stored per host (even if very approximate, like count(*) of rows per host)...

[ingus.vilnis]

Good that you fond this template issue.
1 second for many items was the cause. Check your new values per second now. I think that number should be much smaller after these changes.

Regarding history. Housekeeper will delete the old data for you after the history storage period. Please check that period in your item card. However the free space on disk will not be cleared because of MySql inno db specifics, but the DB space will be filled with other data.

As far as I know there is not a possibility to view the history usage per host in a way you described.

Best Regards,
Ingus

[Grrruk]

Good that you fond this template issue.
1 second for many items was the cause. Check your new values per second now. I think that number should be much smaller after these changes.

Regarding history. Housekeeper will delete the old data for you after the history storage period. Please check that period in your item card. However the free space on disk will not be cleared because of MySql inno db specifics, but the DB space will be filled with other data.
Best Regards,
Ingus

Well, the trouble is, I went through all hosts, checked all items and chose "clear history" for them. It took a lot of time! Unfortunately, when I finally stopped Zabbix and did mysqldump, it still took 3gb in sql form and then, when I deleted database and re-created it from dump file it took again 7.7 gig.... It seems that history_uint is full of something that I can't delete. It just sits there and consumes disk space (and since it survived dump & restore procedure it's not a free space inside mysql db file).

Is it safe to just truncate history_uint table from mysql command line? Will it break anything?

[ingus.vilnis]

Oh, you could delete the history for items just with a few clicks. You could open the item list, select all item checkboxes and the select Clear history for selected in bottom left dropdown.

History_unit table contains values received for items having type Unsigned Integer.
I am not 100% sure but I think you can truncate it without breaking anything.

The other option I had in mind for you is that if you have not done much monitoring and are not afraid to lose the history, you could export all your hosts and templates to XML and then start a new empty DB with importing the XML files and starting over again.

Best Regards,
Ingus

[Grrruk]

Oh, you could delete the history for items just with a few clicks. You could open the item list, select all item checkboxes and the select Clear history for selected in bottom left dropdown.

That's what I did for each host. But a) it did not help and b) HTTP request just timed out while clearing some hosts.

History_unit table contains values received for items having type Unsigned Integer.
I am not 100% sure but I think you can truncate it without breaking anything.

The other option I had in mind for you is that if you have not done much monitoring and are not afraid to lose the history, you could export all your hosts and templates to XML and then start a new empty DB with importing the XML files and starting over again.

Best Regards,
Ingus

I don't mind to start anew, but I drew some maps and that was the most tedious part of whole setup.

BTW, truncate did not work either. It just times out. I did "rename & drop" trick, creating "like" database, but at this point I don't know if I broke anything or not. DB is back to few hundred MBs. We'll see tomorrow...

[ingus.vilnis]

Ok, you can try with a new DB or check the status of current one.

Regarding maps - you can export them in XML format form the existing setup as well.

Best Regards,
Ingus