-
Problem report
-
Resolution: Unresolved
-
Critical
-
None
-
5.4.2
-
None
Steps to reproduce:
- Run Zabbix on version 4.4 and MariaDB 10.4.20
- Add some Ressources (triggers)
- Update Zabbix to 5.4
- Let Zabbix Migrate DB until 99%
- Enjoy Slow queries (~1500s) for days / weeks to come
Result:
970:20210730:124312.627 completed 99% of database upgrade 970:20210730:130245.232 slow query: 1172.484390 sec, "select distinct i2.key_ from items i join item_discovery id on id.itemid=i.itemid join items i2 on id.parent_itemid=i2.itemid join functions f on i.itemid=f.itemid and f.triggerid=XXXX2 where i.flags=2" 970:20210730:132219.251 slow query: 1174.017700 sec, "select distinct i2.key_ from items i join item_discovery id on id.itemid=i.itemid join items i2 on id.parent_itemid=i2.itemid join functions f on i.itemid=f.itemid and f.triggerid=XXXX3 where i.flags=2" 970:20210730:134144.385 slow query: 1165.132978 sec, "select distinct i2.key_ from items i join item_discovery id on id.itemid=i.itemid join items i2 on id.parent_itemid=i2.itemid join functions f on i.itemid=f.itemid and f.triggerid=XXXX4 where i.flags=2"
Because of this the DB does not upgrade completely and therefore Zabbix has a huge downtime (in our case weeks for 28000 triggers).
Expected:
My SQL is a bit rusty but I'm pretty sure that it is possible to do better than a double join query to get the wanted info (I might be wrong though). But still almost half an hour for a select seems wrong to me.
MariaDB [DBZBX]> SELECT DISTINCT i2.key_ FROM items i2 WHERE i2.itemid IN (SELECT id.parent_itemid FROM item_discovery id WHERE id.itemid IN (SELECT i.itemid FROM items i WHERE i.itemid IN (SELECT f.itemid FROM functions f WHERE f.triggerid=XXXX6) AND i.flags=2)); +------------------+ | key_ | +------------------+ | memory.discovery | +------------------+ 1 row in set (0.010 sec) MariaDB [DBZBX]> select distinct i2.key_ from items i join item_discovery id on id.itemid=i.itemid join items i2 on id.parent_itemid=i2.itemid join functions f on i.itemid=f.itemid and f.triggerid=XXXX6 where i.flags=2; +------------------+ | key_ | +------------------+ | memory.discovery | +------------------+ 1 row in set (23 min 25.798 sec)
It might be interesting to fix this before the next LTS Release.
I hope that my Issue is well written enough if not I can rewrite it with more / less info.
Contact me if you need any extra info.
