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

Monitoring -> hosts optimisation

XMLWordPrintable

    • Team C
    • Sprint 103 (Aug 2023), Sprint 104 (Sep 2023)
    • 0.5

      Here is slow query

      mysql> SELECT DISTINCT ht.templateid FROM hosts_templates ht WHERE ht.hostid IN (     SELECT h.hostid     FROM hosts h     WHERE h.flags IN (0,4)         AND h.status IN (0,1)         AND EXISTS (             SELECT NULL             FROM hosts_groups hgg                 JOIN rights r ON r.id=hgg.groupid AND r.groupid=152             WHERE h.hostid=hgg.hostid             GROUP BY hgg.hostid             HAVING MIN(r.permission)>0 AND MAX(r.permission)>=2         ) );
      +------------+
      | templateid |
      +------------+
      |      10440 |
      |      25519 |
      |      30551 |
      |      13324 |
      |      10504 |
      |      10464 |
      |      22142 |
      |      22143 |
      |      25145 |
      |      36200 |
      |      10490 |
      |      29380 |
      |      10705 |
      |      10496 |
      |      10458 |
      |      36505 |
      |      27209 |
      |      12426 |
      |      10509 |
      |      24377 |
      |      27178 |
      |      33479 |
      +------------+
      22 rows in set (2.64 sec)
      
      mysql> EXPLAIN ANALYZE SELECT DISTINCT ht.templateid FROM hosts_templates ht WHERE ht.hostid IN (     SELECT h.hostid     FROM hosts h     WHERE h.flags IN (0,4)         AND h.status IN (0,1)         AND EXISTS (             SELECT NULL             FROM hosts_groups hgg                 JOIN rights r ON r.id=hgg.groupid AND r.groupid=152             WHERE h.hostid=hgg.hostid             GROUP BY hgg.hostid             HAVING MIN(r.permission)>0 AND MAX(r.permission)>=2         ) );
      
      | EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
      | -> Table scan on <temporary>  (cost=4299.74..4345.36 rows=3450) (actual time=2735.621..2735.624 rows=22 loops=1)
          -> Temporary table with deduplication  (cost=4299.73..4299.73 rows=3450) (actual time=2735.619..2735.619 rows=22 loops=1)
              -> Nested loop inner join  (cost=3954.72 rows=3450) (actual time=251.258..2735.198 rows=638 loops=1)
                  -> Filter: ((h.flags in (0,4)) and (h.`status` in (0,1)) and exists(select #3))  (cost=2783.20 rows=3060) (actual time=251.242..2733.563 rows=399 loops=1)
                      -> Table scan on h  (cost=2783.20 rows=26497) (actual time=0.090..17.028 rows=26285 loops=1)
                      -> Select #3 (subquery in condition; dependent)
                          -> Limit: 1 row(s)  (cost=4.86 rows=0.3) (actual time=0.108..0.108 rows=0 loops=24917)
                              -> Filter: ((min(r.permission) > 0) and (max(r.permission) >= 2))  (cost=4.86 rows=0.3) (actual time=0.108..0.108 rows=0 loops=24917)
                                  -> Group aggregate: max(r.permission), min(r.permission)  (cost=4.86 rows=0.3) (actual time=0.108..0.108 rows=0 loops=24917)
                                      -> Nested loop inner join  (cost=4.84 rows=0.3) (actual time=0.106..0.107 rows=0 loops=24917)
                                          -> Covering index lookup on hgg using hosts_groups_1 (hostid=h.hostid)  (cost=0.82 rows=5) (actual time=0.003..0.005 rows=5 loops=24917)
                                          -> Filter: (r.groupid = 152)  (cost=0.57 rows=0.05) (actual time=0.019..0.019 rows=0 loops=132861)
                                              -> Index lookup on r using rights_2 (id=hgg.groupid)  (cost=0.57 rows=2) (actual time=0.007..0.018 rows=8 loops=132861)
                  -> Covering index lookup on ht using hosts_templates_1 (hostid=h.hostid)  (cost=0.27 rows=1) (actual time=0.003..0.004 rows=2 loops=399)
       |
      1 row in set, 1 warning (2.74 sec)
      

      Can be optimized

      mysql> SELECT DISTINCT ht.templateid
          -> FROM hosts_templates ht,hosts h,hosts_groups hg,rights r
          -> WHERE ht.hostid=h.hostid
          ->     AND h.hostid=hg.hostid
          ->     AND hg.groupid=r.id
          ->     AND r.groupid=152
          ->     AND r.permission>=2
          ->     AND h.flags IN (0,4)
          ->     AND h.status IN (0,1)
          ->     AND EXISTS (
          ->         SELECT NULL
          ->         FROM hosts_groups hgg
          ->             JOIN rights r2 ON r2.id=hgg.groupid AND r2.groupid=152
          ->         WHERE h.hostid=hgg.hostid
          ->         GROUP BY hgg.hostid
          ->         HAVING MIN(r2.permission)>0 AND MAX(r2.permission)>=2
          ->     );
      +------------+
      | templateid |
      +------------+
      |      13324 |
      |      25519 |
      |      30551 |
      |      36200 |
      |      10440 |
      |      10458 |
      |      36505 |
      |      10464 |
      |      10490 |
      |      29380 |
      |      12426 |
      |      10705 |
      |      10504 |
      |      10496 |
      |      22142 |
      |      22143 |
      |      25145 |
      |      27209 |
      |      10509 |
      |      27178 |
      |      24377 |
      |      33479 |
      +------------+
      22 rows in set (0.17 sec)
      
      mysql> EXPLAIN ANALYZE SELECT DISTINCT ht.templateid FROM hosts_templates ht,hosts h,hosts_groups hg,rights r WHERE ht.hostid=h.hostid     AND h.hostid=hg.hostid     AND hg.groupid=r.id     AND r.groupid=152     AND r.permission>=2     AND h.flags IN (0,4)     AND h.status IN (0,1)     AND EXISTS (         SELECT NULL         FROM hosts_groups hgg             JOIN rights r2 ON r2.id=hgg.groupid AND r2.groupid=152         WHERE h.hostid=hgg.hostid         GROUP BY hgg.hostid         HAVING MIN(r2.permission)>0 AND MAX(r2.permission)>=2     );
      
      | EXPLAIN |
      | -> Table scan on <temporary>  (cost=977.67..981.48 rows=108) (actual time=140.565..140.568 rows=22 loops=1)
          -> Temporary table with deduplication  (cost=977.63..977.63 rows=108) (actual time=140.563..140.563 rows=22 loops=1)
              -> Nested loop inner join  (cost=966.80 rows=108) (actual time=0.563..140.006 rows=1421 loops=1)
                  -> Nested loop inner join  (cost=929.99 rows=96) (actual time=0.554..137.096 rows=770 loops=1)
                      -> Nested loop inner join  (cost=638.62 rows=832) (actual time=0.205..2.385 rows=770 loops=1)
                          -> Filter: (r.permission >= 2)  (cost=9.92 rows=12) (actual time=0.157..0.172 rows=35 loops=1)
                              -> Index lookup on r using rights_1 (groupid=152)  (cost=9.92 rows=35) (actual time=0.156..0.165 rows=35 loops=1)
                          -> Index lookup on hg using hosts_groups_2 (groupid=r.id)  (cost=47.37 rows=71) (actual time=0.025..0.061 rows=22 loops=35)
                      -> Filter: ((h.flags in (0,4)) and (h.`status` in (0,1)) and exists(select #2))  (cost=0.25 rows=0.1) (actual time=0.175..0.175 rows=1 loops=770)
                          -> Single-row index lookup on h using PRIMARY (hostid=hg.hostid)  (cost=0.25 rows=1) (actual time=0.004..0.004 rows=1 loops=770)
                          -> Select #2 (subquery in condition; dependent)
                              -> Limit: 1 row(s)  (cost=4.86 rows=0.3) (actual time=0.169..0.169 rows=1 loops=770)
                                  -> Filter: ((min(r2.permission) > 0) and (max(r2.permission) >= 2))  (cost=4.86 rows=0.3) (actual time=0.169..0.169 rows=1 loops=770)
                                      -> Group aggregate: max(r2.permission), min(r2.permission)  (cost=4.86 rows=0.3) (actual time=0.169..0.169 rows=1 loops=770)
                                          -> Nested loop inner join  (cost=4.84 rows=0.3) (actual time=0.073..0.167 rows=3 loops=770)
                                              -> Covering index lookup on hgg using hosts_groups_1 (hostid=h.hostid)  (cost=0.82 rows=5) (actual time=0.004..0.007 rows=9 loops=770)
                                              -> Filter: (r2.groupid = 152)  (cost=0.57 rows=0.05) (actual time=0.017..0.018 rows=0 loops=6766)
                                                  -> Index lookup on r2 using rights_2 (id=hgg.groupid)  (cost=0.57 rows=2) (actual time=0.008..0.018 rows=7 loops=6766)
                  -> Covering index lookup on ht using hosts_templates_1 (hostid=hg.hostid)  (cost=0.27 rows=1) (actual time=0.003..0.003 rows=2 loops=770)
       |
      1 row in set, 1 warning (0.14 sec)
      

            sasha Alexander Vladishev
            neogan Andrei Gushchin (Inactive)
            Team C
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: