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

SVG graphs on dashboards use non-optimal query

    XMLWordPrintable

Details

    • Team B
    • Sprint 63 (Apr 2020), Sprint 64 (May 2020), Sprint 65 (Jun 2020), Sprint 66 (Jul 2020), Sprint 67 (Aug 2020), Sprint 68 (Sep 2020), Sprint 69 (Oct 2020), Sprint 70 (Nov 2020), Sprint 71 (Dec 2020), Sprint 72 (Jan 2021), Sprint 73 (Feb 2021), Sprint 74 (Mar 2021), Sprint 75 (Apr 2021), Sprint 92 (Sep 2022), Sprint 93 (Oct 2022), Sprint 94 (Nov 2022), Sprint 95 (Dec 2022), Sprint 96 (Jan 2023)
    • 0.5

    Description

      The SVG graphs on dashboards generates queries where the PRIMARY query cannot use an index:

      mysql> explain SELECT h.hostid FROM hosts h WHERE h.flags IN (0,4) AND EXISTS (SELECT NULL FROM hosts_groups hgg JOIN rights r ON r.id=hgg.groupid AND r.groupid IN (100100000000028,100100000000031,100100000000050,100100000000099,100100000000224) WHERE h.hostid=hgg.hostid GROUP BY hgg.hostid HAVING MIN(r.permission)>0 AND MAX(r.permission)>='2') AND h.status IN (0,1) AND ( (  UPPER(h.name)  LIKE 'HOSTNAME' ESCAPE '!' ) );
      +----+--------------------+-------+------------+------+-------------------------------+----------------+---------+--------------------+-------+----------+--------------------------+
      | id | select_type        | table | partitions | type | possible_keys                 | key            | key_len | ref                | rows  | filtered | Extra                    |
      +----+--------------------+-------+------------+------+-------------------------------+----------------+---------+--------------------+-------+----------+--------------------------+
      |  1 | PRIMARY            | h     | NULL       | ALL  | hosts_2                       | NULL           | NULL    | NULL               | 31598 |    10.01 | Using where              |
      |  2 | DEPENDENT SUBQUERY | hgg   | NULL       | ref  | hosts_groups_1,hosts_groups_2 | hosts_groups_1 | 8       | zabbix.h.hostid    |     3 |   100.00 | Using where; Using index |
      |  2 | DEPENDENT SUBQUERY | r     | NULL       | ref  | rights_1,rights_2             | rights_2       | 8       | zabbix.hgg.groupid |     4 |    19.61 | Using where              |
      +----+--------------------+-------+------------+------+-------------------------------+----------------+---------+--------------------+-------+----------+--------------------------+
      

      Dashboards update frequently, so any non-optmized query can generate a lot of load on the DB.

      Changing the hostname comparison would allow using the index, with a cost of being case sensitive:

      mysql> explain SELECT h.hostid FROM hosts h WHERE h.flags IN (0,4) AND EXISTS (SELECT NULL FROM hosts_groups hgg JOIN rights r ON r.id=hgg.groupid AND r.groupid IN (100100000000028,100100000000031,100100000000050,100100000000099,100100000000224) WHERE h.hostid=hgg.hostid GROUP BY hgg.hostid HAVING MIN(r.permission)>0 AND MAX(r.permission)>='2') AND h.status IN (0,1) AND ( ( h.name  = 'nlsqlext008' ) );
      +----+--------------------+-------+------------+------+-------------------------------+----------------+---------+--------------------+------+----------+--------------------------+
      | id | select_type        | table | partitions | type | possible_keys                 | key            | key_len | ref                | rows | filtered | Extra                    |
      +----+--------------------+-------+------------+------+-------------------------------+----------------+---------+--------------------+------+----------+--------------------------+
      |  1 | PRIMARY            | h     | NULL       | ref  | hosts_2,hosts_4               | hosts_4        | 386     | const              |    1 |    10.01 | Using where              |
      |  2 | DEPENDENT SUBQUERY | hgg   | NULL       | ref  | hosts_groups_1,hosts_groups_2 | hosts_groups_1 | 8       | zabbix.h.hostid    |    3 |   100.00 | Using where; Using index |
      |  2 | DEPENDENT SUBQUERY | r     | NULL       | ref  | rights_1,rights_2             | rights_2       | 8       | zabbix.hgg.groupid |    4 |    19.61 | Using where              |
      +----+--------------------+-------+------------+------+-------------------------------+----------------+---------+--------------------+------+----------+--------------------------+
      

      Attachments

        1. data_error.txt
          2.98 MB
        2. Database load.png
          Database load.png
          61 kB
        3. image-2022-10-04-10-02-07-381.png
          image-2022-10-04-10-02-07-381.png
          34 kB
        4. MicrosoftTeams-image (5).png
          MicrosoftTeams-image (5).png
          22 kB
        5. schema_error_number_2.txt
          14 kB
        6. schema_error.txt
          14 kB

        Issue Links

          Activity

            People

              jfreibergs Janis Freibergs
              kdaudt Kevin Daudt
              Votes:
              10 Vote for this issue
              Watchers:
              32 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: