Loading...

Type: Problem report
Resolution: Won't fix
Priority: Blocker
Fix Version/s: 4.0.8rc1, 4.2.2rc1, 4.4.0alpha1, 4.4 (plan)
Affects Version/s: 4.0.5
Component/s: Frontend (F)
Labels:
None

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

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)

Details

Description

Attachments

Activity

People

Dates