Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-19792

Slow query Zabbix Update 4.4 --> 5.4

    XMLWordPrintable

Details

    • Problem report
    • Status: Confirmed
    • Critical
    • Resolution: Unresolved
    • 5.4.2
    • None
    • Server (S)
    • None

    Description

      Steps to reproduce:

      1. Run Zabbix on version 4.4 and MariaDB 10.4.20
      2. Add some Ressources (triggers)
      3. Update Zabbix to 5.4
      4. Let Zabbix Migrate DB until 99%
      5. 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.

      Attachments

        Activity

          People

            zabbix.dev Zabbix Development Team
            aegger Alexis EGGER
            Votes:
            2 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated: