Zabbix hosekeeping job taking longer than 1h

[Nilufar]

Hello zabbix team!
I have encountered strange problem with housekeeper process in my zabbix server, the situation is as following: During the day the housekeeper is pretty efficient up to ~ 20 max, but during the night starting from 1 AM it's running and taking longer than 1h which is max ~3h and min ~1,5h. Today it was the same situation where my all pm.max_children for php-fpm was reached its max number, I increased it and restarted php-fpm, but it was the same again. In my mariadb database I found several SQL queries from houseekeeping job which took more than 700s to complete the query and there were several of them. We decided to kill those sql queries using their process ID, after that it started to work but I was expecting by the next housekeeper cycle i will face the same problem again because I killed those queries and the data was not deleted properly, but the next housekeeper process didn't took more than 11m to finish its job. How can I determine why every night houskeeper is taking long hours to finish ?
Thanks in advance!

[cyber]

Is there any backups in progress at the time you experience issues? Some DB dumps that lock DB or something?

[Nilufar]

Hello cyber ! Thanks for your reply, I disabled the backup job and observed that at 12:35 AM the housekeeping job was about 1h 46m which is again more than 1h but not full 2h as it usually happened before when backup was enabled every night at the same time, do you know any methods to track what exactly is expiring at the same time every night the same time? Thanks for any ideas and suggestions.

[tim.mooney]

Cyber's idea was a good one, and because of the time of day you're experiencing the problem it's also the first thing I thought of.

You don't say anything about your environment, but if your Zabbix database is hosted in a shared environment, either as a VM that shares I/O operations with other systems or a physical server that has the database on SAN or NAS storage that may share I/O with other hosts, then the problem may be happening not because of anything on the Zabbix system. It could be because other systems that share the same I/O zone are doing backups, or log file rotation, or other overnight processing that is I/O intensive.

If your database storage is shared with other hosts, I recommend you look at the I/O utilization for that storage.