Zabbix 3.2 - HouseKeeping

[pradeepspa]

Folks,

I am experiencing performance issue in zabbix. I have deployed around 400 host and 1000s of items. I configured history and trend for 90 and 365 and later I changed history to 30. However, i couldnt find in logs how many records has been deleted by housekeeper. I enabled debug to "2" but still couldn't find in logs. All i can see related to history/housekeeper is,

11247:20180214:121027.403 server #335 started [task manager #1]
11239:20180214:121027.404 server #327 started [history syncer #6]
11241:20180214:121027.405 server #329 started [history syncer #8]
11244:20180214:121027.405 server #332 started [self-monitoring #1]
11218:20180214:121027.405 server #307 started [icmp pinger #5]
11240:20180214:121027.406 server #328 started [history syncer #7]
11243:20180214:121027.406 server #331 started [proxy poller #1]
11229:20180214:121027.409 server #318 started [discoverer #2]
11221:20180214:121027.414 server #310 started [icmp pinger #8]
11225:20180214:121027.414 server #314 started [housekeeper #1]

I am trying to delete records more than 180 days from history_uint table but its taking forever.

Here is my zabbix status.


Can someone tell me where can I see number of records being deleted by housekeeper?

[vesper1978]

You should be seeing something like this, even with the logging at the default:

31980:20180214:054019.785 executing housekeeper
31980:20180214:054050.989 housekeeper [deleted 532672 hist/trends, 0 items/triggers, 409 events, 241 problems, 0 sessions, 0 alarms, 0 audit items in 31.188874 sec, idle for 1 hour(s)]

Sounds like there's just too much for housekeeper to delete, and it may possibly be failing.

What kind of resources have you dedicated to your database? You may need to add more cpu, memory and use faster disks.

[pradeepspa]

Thanks for your response.
I am using postgres database.

System Resource

Disk : 350GB
CPU : 8
RAM : 9GB

I am new to postgres , could you tell me what needs to be tuned there?

[vesper1978]

I don't use postgres, but you need way more memory. 350GB of storage doesn't tell me anything about the speed of the disks, just the size. Use SSDs for databases.

[pradeepspa]

Unfortunately i dont have SSDs. I tried deleting the content of history logged more than 180 days but its taking forever. Is there any other way to cleanup the database? I feel once I do a manual cleanup, housekeeper process can handle the load.

[vesper1978]

The speed of your disks is going to be a huge bottleneck.

You can try deleting records more than 180 days old from history_uint directly, and limit the number of records you delete at a time. You can start small, then increase to try and find how many you can delete in a reasonable time at once. I don't know postgres, so I wouldn't be able to help you craft that delete statement.

[pradeepspa]

Thanks. I will figure out the command and try that.

[pradeepspa]

I tried many methods but none works considering the size of the table. Therefore I dropped the table and created a new one. Right now the disk usage is under control. But watching the logs, I dont see housekeeper is running. Is there anything else I can do to run housekeeping process?

[tcilmo]

There is also a bug that could be contributing to performance problems.

https://support.zabbix.com/browse/ZBX-13343

[tcilmo]

I tried many methods but none works considering the size of the table. Therefore I dropped the table and created a new one. Right now the disk usage is under control. But watching the logs, I dont see housekeeper is running. Is there anything else I can do to run housekeeping process?

Housekeeper will run every hour, but it can be manually started. I would lower your retention period for your environment to like 90 days, or use database partitioning on history \ trends - that way you can turn off housekeeping on those tables. Just dropping a table is like liposuction. It won’t do any good if you can’t burn off what you take in.

Another performance tunning option would be to use a proxy - if you are not doing so already.

[kaspars.mednis]

Hi all,

when your DB size grows too big and disks are too slow to delete historical data, go to partitioning

https://zabbix.org/wiki/Docs/howto/mysql_partitioning (Partitioning with an external script)

If you follow the manual, its really simple...
After that, you will be deleting data in seconds...

Regards,
Kaspars

[pradeepspa]

Housekeeper will run every hour, but it can be manually started. I would lower your retention period for your environment to like 90 days, or use database partitioning on history \ trends - that way you can turn off housekeeping on those tables. Just dropping a table is like liposuction. It won’t do any good if you can’t burn off what you take in.

Another performance tunning option would be to use a proxy - if you are not doing so already.

Thanks for the information. I have configured partition and proxy too. Performance looks lot better. Could you tell me how can I turn off housekeeping for specific table?

[tcilmo]

Take a look at this documentation in regards to MySQL database partitioning for history and trends.

https://zabbix.org/wiki/Docs/howto/mysql_partition

[pradeepspa]

Take a look at this documentation in regards to MySQL database partitioning for history and trends.

https://zabbix.org/wiki/Docs/howto/mysql_partition

I am using postgres database and I followed this doc for partition.

https://www.zabbix.org/wiki/Docs/howto/zabbix2_postgresql_autopartitioning

I thought there will be a way in config that housekeeping can be turned off for specific tables.

[tcilmo]

I am using postgres database and I followed this doc for partition.

https://www.zabbix.org/wiki/Docs/howto/zabbix2_postgresql_autopartitioning

I thought there will be a way in config that housekeeping can be turned off for specific tables.

My bad, we use MySQL so that is where my brain went. Under Administration > General > Housekeeping (drop down on left hand side of screen).

Here you can enable\disable housekeeping on different items suck as history and trends. Keep in mind if you disable house keeping on history and trends and if you don't use partitioning your database will grow until you run out of disk.