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

Query regression on "Host status" dashboard widget for huge installations

    Details

      Description

      mysql> select count from hosts;
      ----------

      count

      ----------

      18294

      ----------
      1 row in set (0.00 sec)

      mysql> select count from groups;
      ----------

      count

      ----------

      26

      ----------
      1 row in set (0.00 sec)

      mysql> select count from hosts_groups;
      ----------

      count

      ----------

      54822

      ----------
      1 row in set (0.01 sec)

      mysql> explain SELECT DISTINCT h.hostid,h.name,hg.groupid FROM hosts h,hosts_groups hg USE INDEX ( hosts_groups_2) WHERE (hg.groupid IN ('148','25','24','21','147','11','9','26','29','28','14','19','12','18','13','10','16','15','20','17','22','4')) AND hg.hostid=h.hostid AND h.status=0 AND h.hostid BETWEEN 000000000000000 AND 099999999999999;
      --------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      --------------------------------------------------------------------------------------------------------+

      1 SIMPLE hg ALL hosts_groups_2 NULL NULL NULL 55669 Using where; Using temporary
      1 SIMPLE h eq_ref PRIMARY,hosts_2 PRIMARY 8 zabbix.hg.hostid 1 Using where

      --------------------------------------------------------------------------------------------------------+
      2 rows in set (0.00 sec)

      This query executes ~0.5seconds. This query does not use indexes for "hosts_groups" table.

      mysql> explain SELECT DISTINCT h.hostid,h.name,hg.groupid FROM hosts h,hosts_groups hg WHERE (hg.groupid IN ('148','25','24','21','147','11','9','26','29','28','14','19','12','18','13','10','16','15','20','17','22','4')) AND hg.hostid=h.hostid AND h.status=0 AND h.hostid BETWEEN 000000000000000 AND 099999999999999;
      --------------------------------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      --------------------------------------------------------------------------------------------------------------------------------+

      1 SIMPLE h ref PRIMARY,hosts_2 hosts_2 4 const 8979 Using where; Using temporary
      1 SIMPLE hg range hosts_groups_1,hosts_groups_2 hosts_groups_1 16 NULL 27834 Using where; Using index; Using join buffer

      --------------------------------------------------------------------------------------------------------------------------------+
      2 rows in set (0.00 sec)

      The new query executes ~142seconds.

      Mysql query optimizer uses "hosts_groups_1". But query selects from "hosts" table almost all of data. So this query executes much longer.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              dotneft Alexey Pustovalov
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: