We have such number of rows in corresponding tables:
hosts - 423722
interface - 620132
dservices - 360533
dchecks - 18123
drules - 18123
Yes, 18K network discovery rules, each assigned to own proxy (yes, 18K proxies).
Each rule scan this ranges each 3 hours: 192.168.140.1-254,192.168.150.1-39 (~300 IPs).
The problem that we always have many heavy queries from this function:
namely 2nd query - with filtering by ds.dserviceid we could spot in waiting transaction.
On average these queries take 15 seconds and cause high CPU usage on DB server.
As I see in my debug test each query from the function is repeated once (per check and per rule). I.e. two very similar heavy queries.
I've figure out that this SQL comes from operation to delete a host, when zabbix server needs to check host existence.
The operation (and the function call) is performed for IPs when host is deleted already and IP is still not responsive (check is failed).
Maybe the SQL could be improved to be faster and avoid high CPU usage?