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

Very Long Running Query to Delete template

XMLWordPrintable

    • Sprint 50 (Mar 2019)
    • 0.5

      Steps to reproduce:

      • Attempt to delete a template with a large number of hosts in Zabbix

      Result:
      I received a timeout (after 30 minutes) trying to delete a single template in our Zabbix environment, the template had 0 hosts or templates linked to it, so I expected this to be a quick procedure.
      Expected:
      The template to be deleted.

       

       

      I checked the running processes in our DB and found the query to pull out graphs linked tot he template still running.  I believe the issue is due to the query joining with the hosts table, without hosts specified for links to other tables.  I ran the query manually and it took over 1 hour to complete, returning 0 results:

      MariaDB [zabbix_infra]> explain SELECT DISTINCT g.graphid,g.name,g.flags,h.name as host FROM graphs g,hosts h WHERE EXISTS (SELECT ggi.graphid FROM graphs_items ggi,items ii WHERE ggi.graphid=g.templateid AND ii.itemid=ggi.itemid AND ii.hostid='100252') AND g.flags IN ('0','1');

      +------+--------------+-------+-------+---------------------------------------------------------+----------------+---------+------------------------+--------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+-------+-------+---------------------------------------------------------+----------------+---------+------------------------+--------+------------------------------+ | 1 | PRIMARY | h | index | NULL | hosts_4 | 386 | NULL | 33957 | Using index; Using temporary | | 1 | PRIMARY | g | ALL | NULL | NULL | NULL | NULL | 314946 | Using where | | 2 | MATERIALIZED | ii | ref | PRIMARY,items_1,items_3,items_4,items_5,items_6,items_7 | items_1 | 8 | const | 1 | Using index | | 2 | MATERIALIZED | ggi | ref | graphs_items_1,graphs_items_2 | graphs_items_1 | 8 | zabbix_infra.ii.itemid | 1 | | +------+--------------+-------+-------+---------------------------------------------------------+----------------+---------+------------------------+--------+------------------------------+ 4 rows 
      

      in set (0.00 sec) MariaDB [zabbix_infra]> SELECT DISTINCT g.graphid,g.name,g.flags,h.name as host FROM graphs g,hosts h WHERE EXISTS (SELECT ggi.graphid FROM graphs_items ggi,items ii WHERE ggi.graphid=g.templateid AND ii.itemid=ggi.itemid AND ii.hostid='100252') AND g.flags IN ('0','1'); Empty set (1 hour 22 min 1.47 sec)

       

      If I add a hostid condition to the query it returns immediately:

      MariaDB [zabbix_infra]> SELECT DISTINCT g.graphid,g.name,g.flags,h.name as host FROM graphs g,hosts h WHERE EXISTS (SELECT ggi.graphid FROM graphs_items ggi,items ii WHERE ggi.graphid=g.templateid AND ii.itemid=ggi.itemid AND ii.hostid='100252') AND g.flags IN ('0','1') and h.hostid = 100252;

      Empty set (0.25 sec)

       

      Would it be possible to add the h.hostid conditions when building this query?

      Thanks!

      Eric

            sasha Alexander Vladishev
            audioguy Eric Johnson
            Team B
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved: