Zabbix Database Too Big!

[[email protected]]

We are monitoring 70 Linux servers with the "Zabbix by Linux Agent," 20 servers with "Zabbix by Windows Agent," 10 switches with "SNMP Network," and about 400 MySQL databases using "MySQL by ODBC." The Zabbix history logs get big way too fast.

Currently it is 131GB and has filled the disk. Here are the biggest tables.

-rw-r-----. 1 mysql mysql 75G Apr 15 00:10 history.ibd
-rw-r-----. 1 mysql mysql 37G Apr 15 00:10 history_uint.ibd
-rw-r-----. 1 mysql mysql 13G Apr 18 00:00 trends.ibd
-rw-r-----. 1 mysql mysql 6.2G Apr 18 00:00 trends_uint.ibd


Is that normal? Is there a way to expunge old data?

[cyber]

Your db size depends on amount of items, type of items, on interval of items, on history keeping period.... So don't check too often, don't keep history longer than you really need... Don't keep text data in DB...
Trend tables will not change from that, as they will still keep one data point per hour for each item...

[[email protected]]

Your db size depends on amount of items, type of items, on interval of items, on history keeping period.... So don't check too often, don't keep history longer than you really need... Don't keep text data in DB...
Trend tables will not change from that, as they will still keep one data point per hour for each item...

How do I expunge old data and reduce the table sizes?

[cyber]

Sorry, don't know mysql well enough to say something useful..

[tim.mooney]

How do I expunge old data and reduce the table sizes?

Database sizing and estimating how much space your install will need based on the criteria that Cyber mentioned is covered in the Zabbix documentation. The Zabbix housekeeper process, which cleans up historical data (and rolls up data into trends) is also covered in the Zabbix documentation.

If you don't fix your "I'm keeping too much data for too long" problem first, then expunging existing records isn't going to do much good, because Zabbix will just eventually keep new data and you'll be back to where you are now. Reduce your collection frequency and/or how long you keep various items first, as Cyber explained.

Once you have the collection and retention issues fixed, the housekeeper will eventually "catch up", deleting a large portion of the rows in your history, history_uint, etc. tables. It may take a long time for that to happen, though. If you cannot wait that long, then you'll need to craft "DELETE FROM" queries with a suitable "WHERE" clause to manually delete rows from your history, history_uint, etc. tables.

Once you've freed a large number of rows within your history and history_uint tables, to reclaim the space on disk, look at the OPTIMIZE TABLE mysql/mariadb statement. Be sure you have at least 2x the size of the table available in free space on the volume that holds your database.

Whatever you do, do not TRUNCATE either of those tables. They are required for Zabbix to work. There is a way you could create new empty versions of those tables, do a quick RENAME to move the huge table out of the way and put the empty table into place, and then just DROP the old huge table, but that's a more complicated process.

[[email protected]]

If you cannot wait that long, then you'll need to craft "DELETE FROM" queries with a suitable "WHERE" clause to manually delete rows from your history, history_uint, etc. tables.

That's the question I'm hoping someone can answer.

[tim.mooney]

Do some searching on these forums, the question has been asked and answered (in varying degrees of correctness and information) one possible answer: https://www.zabbix.com/forum/zabbix-...nt-and-history