DB Size, Housekeeper running fine, but old history present.
Hi everyone, I'm hoping to get some insight/assistance in my database / housekeeper (so no partitioning yet). My setup seems to run just fine, but I'm worried about database size.
I migrated this weekend from Zabbix 3.4.* to 5.0.6. At the same time I moved from CentOS7 to Debian 10 buster for Zabbix.
The database moved from a Windows MariaDB to the same Debian 10 server.
I have no errors in my server, Mariadb or Zabbix-server (and proxies) logs. (Sometimes I can see a slow query , mostly during housekeeper, usually nothing above 5 seconds).
New values per second is about 600 for little over 1000 hosts.
Agent monitoring is not done by the server but by proxies.
The servers are VM's on the same hardware as before. (RAID10 SSD's), more then enough CPU's, 20GB of RAM for the Zabbix server / Mariadb and Mariadb is using most of it, 4GB available in the guest.
My database size on Zabbix 3.4 has been about 32GB and not really growing.
After migrating to Zabbix 5.0, my DB has been growing at +-1GB a day. Yesterday revisited my templates and reduced item retention and disabled or reduced trends on items (and discovery items) in my templates.
I would have expected housekeeper to free up >10% of my history tables. But the DB keeps growing at the same rate.
Housekeeper runs once an hours.
MaxHousekeeperDelete=1000000 (I even ran it with 0)
When housekeeper runs it deletes +- 212000000 rows each time, when I run it manually to test, it deletes les, so it looks like it has less work to do.
Hoursekeeper runs for about 5 minutes each hour, when I run it manually between that, it runs les then 2 minutes.
I ran "SELECT Count(*) FROM history_uint WHERE clock < NOW() - INTERVAL 365 DAY;" on my DB. If I did this correct, this should show me records that are older then 1 year in my history table. According to the items on my hosts/templates, I should have 0 rows.
However, the result is 212 000 000. This makes me think housekeeper may not be deleting everything that should be deleted.
Housekeeper seems less bussy in 5.0 then in 3.4 though (the gap is the switch Pasteboard - Uploaded Image )
So now the questions
I migrated this weekend from Zabbix 3.4.* to 5.0.6. At the same time I moved from CentOS7 to Debian 10 buster for Zabbix.
The database moved from a Windows MariaDB to the same Debian 10 server.
I have no errors in my server, Mariadb or Zabbix-server (and proxies) logs. (Sometimes I can see a slow query , mostly during housekeeper, usually nothing above 5 seconds).
New values per second is about 600 for little over 1000 hosts.
Agent monitoring is not done by the server but by proxies.
The servers are VM's on the same hardware as before. (RAID10 SSD's), more then enough CPU's, 20GB of RAM for the Zabbix server / Mariadb and Mariadb is using most of it, 4GB available in the guest.
My database size on Zabbix 3.4 has been about 32GB and not really growing.
After migrating to Zabbix 5.0, my DB has been growing at +-1GB a day. Yesterday revisited my templates and reduced item retention and disabled or reduced trends on items (and discovery items) in my templates.
I would have expected housekeeper to free up >10% of my history tables. But the DB keeps growing at the same rate.
Housekeeper runs once an hours.
MaxHousekeeperDelete=1000000 (I even ran it with 0)
When housekeeper runs it deletes +- 212000000 rows each time, when I run it manually to test, it deletes les, so it looks like it has less work to do.
Hoursekeeper runs for about 5 minutes each hour, when I run it manually between that, it runs les then 2 minutes.
I ran "SELECT Count(*) FROM history_uint WHERE clock < NOW() - INTERVAL 365 DAY;" on my DB. If I did this correct, this should show me records that are older then 1 year in my history table. According to the items on my hosts/templates, I should have 0 rows.
However, the result is 212 000 000. This makes me think housekeeper may not be deleting everything that should be deleted.
Housekeeper seems less bussy in 5.0 then in 3.4 though (the gap is the switch Pasteboard - Uploaded Image )
So now the questions
- Can I expect Zabbix 5.0 to simply grow larger then 3.4?
- Is my query correct about the history in the DB and how could I continue to debug this?
- Any other idea's/queries I can perform to monitor/debug this?