After some time you may find that you Zabbix database become enormously big and consumes significant amount of disk space. Among really usefull data there is data that may be cleaned up and optimized in Zabbix database in order to reduce size of Zabbix database.
Configure Zabbix Housekeeping
This settings determine how long to keep historical data in Zabbix tables.
Go to “Administration” -> “General” -> “Housekeeping”
Default values are 365 days. Set this to lower value unless you really need to keep history for one year.
After that wait for Housekeeper’s procedure to complete. You can watch it in a log with:
|
1 |
more /var/log/zabbix/zabbix_server.log | grep house |
Example output:
3050:20240202:121303.581 executing housekeeper
3050:20240202:121556.485 housekeeper [deleted 4847762 hist/trends, 31260 items/triggers, 116 events, 209 problems, 6 sessions, 0 alarms, 0 audit, 0 records in 172.903534 sec, idle for 1 hour(s)]
Housekeeper runs once per hour.
Now entries are deleted from tables but this will not result in free disk space amount as MySQL’s files will remain the same size. It’s like if we pour a water away from a bucket: there is little amount of water in the bucket but the bucket’s size is the same.
If you don’t want to wait for Housekeeper you can run these MySQL quieries:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- intervals in days SET @history_interval = 7; SET @trends_interval = 90; DELETE FROM alerts WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60); DELETE FROM acknowledges WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60); DELETE FROM events WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60); DELETE FROM history WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60); DELETE FROM history_uint WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60); DELETE FROM history_str WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60); DELETE FROM history_text WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60); DELETE FROM history_log WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60); DELETE FROM trends WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@trends_interval * 24 * 60 * 60); DELETE FROM trends_uint WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@trends_interval * 24 * 60 * 60); |
Now we need to replace a “bucket” with a “bottle” and free up disk space.
Optimize (reduce) Zabbix tables
This can be done with MySQL “optimize” statement. It’s will defragment the table and squeeze the table’s file on a disk:
|
1 2 |
MariaDB [zabbix]> use zabbix; MariaDB [zabbix]> optimize table history_text; |
Here are the tables with istorycal data you need to optimize to reduce Zabbix database size:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
optimize table alerts; optimize table acknowledges; optimize table events; optimize table history; optimize table history_uint; optimize table history_str; optimize table history_text; optimize table history_log; optimize table trends; optimize table trends_uint; |
Now check free space and you will notice a difference.
Good luck!
