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

Graphs page creates heavy queries on large instances

    XMLWordPrintable

    Details

    • Team:
      Team D
    • Sprint:
      Sprint 50 (Mar 2019), Sprint 51 (Apr 2019), Sprint 52 (May 2019)
    • Story Points:
      2

      Description

      SHOW PROCESSLIST:

      MariaDB [(none)]> select id, host, state, time, info from information_schema.PROCESSLIST where time >100 and host like 'web' order by 3;
      +----------+-----------------------------------+--------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | id       | host                              | state        | time | info                                                                                                                                                                                                                                                                                                          |
      +----------+-----------------------------------+--------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | 11019707 | web:56902 | Sending data |  195 | SELECT g.groupid,g.name FROM hstgrp g WHERE EXISTS (SELECT NULL FROM hosts h,items i,graphs_items gi,graphs gr,hosts_groups hg WHERE hg.hostid=h.hostid AND h.status IN ('0','1') AND hg.hostid=i.hostid AND i.itemid=gi.itemid AND gi.graphid=gr.graphid AND gr.flags IN ('0','4') AND g.groupid=hg.groupid) |
      | 11018329 | web:54128 | Sending data |  443 | SELECT g.groupid,g.name FROM hstgrp g WHERE EXISTS (SELECT NULL FROM hosts h,items i,graphs_items gi,graphs gr,hosts_groups hg WHERE hg.hostid=h.hostid AND h.status IN ('0','1') AND hg.hostid=i.hostid AND i.itemid=gi.itemid AND gi.graphid=gr.graphid AND gr.flags IN ('0','4') AND g.groupid=hg.groupid) |
      | 11018077 | web:53622 | Sending data |  495 | SELECT g.groupid,g.name FROM hstgrp g WHERE EXISTS (SELECT NULL FROM hosts h,items i,graphs_items gi,graphs gr,hosts_groups hg WHERE hg.hostid=h.hostid AND h.status IN ('0','1') AND hg.hostid=i.hostid AND i.itemid=gi.itemid AND gi.graphid=gr.graphid AND gr.flags IN ('0','4') AND g.groupid=hg.groupid) |
      | 11017537 | web:52530 | Sending data |  599 | SELECT g.groupid,g.name FROM hstgrp g WHERE EXISTS (SELECT NULL FROM hosts h,items i,graphs_items gi,graphs gr,hosts_groups hg WHERE hg.hostid=h.hostid AND h.status IN ('0','1') AND hg.hostid=i.hostid AND i.itemid=gi.itemid AND gi.graphid=gr.graphid AND gr.flags IN ('0','4') AND g.groupid=hg.groupid) |
      | 11017464 | web:37170 | Sending data |  616 | SELECT g.groupid,g.name FROM hstgrp g WHERE EXISTS (SELECT NULL FROM hosts h,items i,graphs_items gi,graphs gr,hosts_groups hg WHERE hg.hostid=h.hostid AND h.status IN ('0','1') AND hg.hostid=i.hostid AND i.itemid=gi.itemid AND gi.graphid=gr.graphid AND gr.flags IN ('0','4') AND g.groupid=hg.groupid) |
      | 11017360 | web:52182 | Sending data |  631 | SELECT g.groupid,g.name FROM hstgrp g WHERE EXISTS (SELECT NULL FROM hosts h,items i,graphs_items gi,graphs gr,hosts_groups hg WHERE hg.hostid=h.hostid AND h.status IN ('0','1') AND hg.hostid=i.hostid AND i.itemid=gi.itemid AND gi.graphid=gr.graphid AND gr.flags IN ('0','4') AND g.groupid=hg.groupid) |
      | 11017049 | web:51530 | Sending data |  697 | SELECT g.groupid,g.name FROM hstgrp g WHERE EXISTS (SELECT NULL FROM hosts h,items i,graphs_items gi,graphs gr,hosts_groups hg WHERE hg.hostid=h.hostid AND h.status IN ('0','1') AND hg.hostid=i.hostid AND i.itemid=gi.itemid AND gi.graphid=gr.graphid AND gr.flags IN ('0','4') AND g.groupid=hg.groupid) |
      | 11016698 | web:36890 | Sending data |  770 | SELECT g.groupid,g.name FROM hstgrp g WHERE EXISTS (SELECT NULL FROM hosts h,items i,graphs_items gi,graphs gr,hosts_groups hg WHERE hg.hostid=h.hostid AND h.status IN ('0','1') AND hg.hostid=i.hostid AND i.itemid=gi.itemid AND gi.graphid=gr.graphid AND gr.flags IN ('0','4') AND g.groupid=hg.groupid) |

      It seems that these queries are created when a user with SuperAdmin privileges open Monitoring>Graphs, the page never loads.
      When restricted users do it, the queries are long, but the page is eventually loaded.
      And also, the page is refreshed according to User refresh preferences even if no graph is selected.

      Size of the instance:

      MariaDB [zabbix]> select count(*) from hstgrp;
      +----------+
      | count(*) |
      +----------+
      |      757 |
      +----------+
      1 row in set (0.01 sec)
      
      MariaDB [zabbix]> select count(*) from hosts;
      +----------+
      | count(*) |
      +----------+
      |    20256 |
      +----------+
      1 row in set (0.01 sec)
      MariaDB [zabbix]> select count(*) from items;
      +----------+
      | count(*) |
      +----------+
      |  6026501 |
      +----------+
      1 row in set (3.36 sec)
      MariaDB [zabbix]> select count(*) from graphs;
      +----------+
      | count(*) |
      +----------+
      |   423360 |
      +----------+
      1 row in set (0.16 sec)
      MariaDB [zabbix]> select count(*) from graphs_items;
      +----------+
      | count(*) |
      +----------+
      |  1227321 |
      +----------+
      1 row in set (0.51 sec)
      MariaDB [zabbix]> select count(*) from hosts_groups;
      +----------+
      | count(*) |
      +----------+
      |    66911 |
      +----------+
      1 row in set (0.05 sec)
      

      Explain for the query:

      MariaDB [zabbix]> EXPLAIN SELECT g.groupid,g.name FROM hstgrp g WHERE EXISTS (SELECT NULL FROM items i,hosts h,functions f,triggers t,hosts_groups hg WHERE hg.hostid=i.hostid AND hg.hostid=h.hostid AND i.itemid=f.itemid AND f.triggerid=t.triggerid AND h.status='0' AND i.status='0' AND t.status='0' AND t.flags IN ('0','4') AND g.groupid=hg.groupid);
      
      +------+--------------------+-------+--------+-------------------------------+----------------+---------+----------------------------------+---------+--------------------------+
      | id   | select_type        | table | type   | possible_keys                 | key            | key_len | ref                              | rows    | Extra                    |+------+--------------------+-------+--------+-------------------------------+----------------+---------+----------------------------------+---------+--------------------------+
      |    1 | PRIMARY            | g     | index  | NULL                          | hstgrp_1       | 257     | NULL                             |     690 | Using where; Using index |
      |    2 | DEPENDENT SUBQUERY | t     | ref    | PRIMARY,triggers_1            | triggers_1     | 4       | const                            | 1237207 | Using where              |
      |    2 | DEPENDENT SUBQUERY | f     | ref    | functions_1,functions_2       | functions_1    | 8       | zabbix.t.triggerid               |       1 |                          |
      |    2 | DEPENDENT SUBQUERY | i     | eq_ref | PRIMARY,items_1,items_3       | PRIMARY        | 8       | zabbix.f.itemid                  |       1 | Using where              |
      |    2 | DEPENDENT SUBQUERY | hg    | eq_ref | hosts_groups_1,hosts_groups_2 | hosts_groups_1 | 16      | zabbix.i.hostid,zabbix.g.groupid |       1 | Using index              |
      |    2 | DEPENDENT SUBQUERY | h     | eq_ref | PRIMARY,hosts_2               | PRIMARY        | 8       | zabbix.i.hostid                  |       1 | Using where              |+------+--------------------+-------+--------+-------------------------------+----------------+---------+----------------------------------+---------+--------------------------+6 rows in set (0.00 sec)
      
      

        Attachments

          Activity

            People

            Assignee:
            gcalenko Gregory Chalenko
            Reporter:
            zux Edgars Melveris
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: