[ZBX-13386] host.get api method SQL improvement Created: 2018 Jan 24  Updated: 2024 Apr 10  Resolved: 2019 Jul 10

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: API (A)
Affects Version/s: 3.4.6
Fix Version/s: None

Type: Problem report Priority: Major
Reporter: Ingus Vilnis Assignee: Zabbix Development Team
Resolution: Won't fix Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
Sub-task
depends on ZBXNEXT-4119 Tag based permissions, responsibility... Closed
Team: Team B
Sprint: Sprint 26

 Description   

While debuging our DB performance, we encountered a SQL statement that were executed 23869 times in one day. It used 44,9% of all time that all queries that took over 1 second that day.

Turns out that this query is used for host.get method (frontends/php/include/classes/api/services/CHost.php), and is probably called for most pages that any user may call every day.

A sample of the query is this:

SELECT DISTINCT h.hostid,h.name,h.status FROM hosts h,hosts_groups hg 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 ('21','45') WHERE h.hostid=hgg.hostid GROUP BY hgg.hostid HAVING MIN(r.permission)>0 AND MAX(r.permission)>='2') AND hg.groupid='174' AND hg.hostid=h.hostid AND h.status=0 AND EXISTS (SELECT NULL FROM items i,functions f,triggers t WHERE h.hostid=i.hostid AND i.itemid=f.itemid AND f.triggerid=t.triggerid AND i.status=0 AND t.status=0 AND t.flags IN (0,4))

Environment details:
– hosts 18352
– hosts_groups 99820
– rights 3179
– items 1330422
– functions 495390
– triggers 279145

Suggestions and discussions in the comments.



 Comments   
Comment by Ingus Vilnis [ 2018 Jan 24 ]

Please review the suggested query:

SELECT DISTINCT h.hostid,h.name,h.status 
FROM hosts h,hosts_groups hg , items i,functions f,triggers t 
WHERE h.flags IN (0,4) 
AND EXISTS (SELECT NULL 
                          FROM  rights r 
                           WHERE r.id=hg.groupid AND r.groupid IN ('21','45') 
                           AND h.hostid=hg.hostid                                                                     
                          GROUP BY hg.hostid HAVING MIN(r.permission) >0 AND MAX(r.permission)>='2') 
AND hg.groupid in('174') AND hg.hostid=h.hostid 
AND h.status=0 and  h.hostid=i.hostid 
AND i.itemid=f.itemid AND f.triggerid=t.triggerid 
AND i.status=0 AND t.status=0 
AND t.flags IN (0,4);
Comment by Marc [ 2018 Jan 24 ]

Just in case it might be interesting, here an execution plan of the criticized SQL statement on PostgreSQL 10.1 with a Zabbix 3.0 data model:

                                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=373.67..373.68 rows=1 width=30) (actual time=0.102..0.102 rows=0 loops=1)
   ->  Sort  (cost=373.67..373.67 rows=1 width=30) (actual time=0.101..0.101 rows=0 loops=1)
         Sort Key: h.hostid, h.name
         Sort Method: quicksort  Memory: 25kB
         ->  Nested Loop Semi Join  (cost=3.20..373.66 rows=1 width=30) (actual time=0.092..0.092 rows=0 loops=1)
               Join Filter: (hg.hostid = i.hostid)
               ->  Nested Loop  (cost=1.80..77.89 rows=2 width=38) (actual time=0.092..0.092 rows=0 loops=1)
                     ->  Index Scan using hosts_groups_2 on hosts_groups hg  (cost=0.29..8.56 rows=7 width=8) (actual time=0.008..0.012 rows=4 loops=1)
                           Index Cond: (groupid = '174'::bigint)
                     ->  Bitmap Heap Scan on hosts h  (cost=1.52..9.90 rows=1 width=30) (actual time=0.018..0.018 rows=0 loops=4)
                           Recheck Cond: (hostid = hg.hostid)
                           Filter: ((flags = ANY ('{0,4}'::integer[])) AND (status = 0) AND (SubPlan 1))
                           Rows Removed by Filter: 1
                           Heap Blocks: exact=4
                           ->  Bitmap Index Scan on hosts_pkey  (cost=0.00..1.52 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=4)
                                 Index Cond: (hostid = hg.hostid)
                           SubPlan 1
                             ->  GroupAggregate  (cost=1.75..7.27 rows=1 width=40) (actual time=0.008..0.008 rows=0 loops=4)
                                   Group Key: hgg.hostid
                                   Filter: ((min(r.permission) > 0) AND (max(r.permission) >= 2))
                                   ->  Hash Join  (cost=1.75..7.25 rows=1 width=12) (actual time=0.007..0.007 rows=0 loops=4)
                                         Hash Cond: (r.id = hgg.groupid)
                                         ->  Index Scan using rights_1 on rights r  (cost=0.28..5.71 rows=5 width=12) (actual time=0.006..0.006 rows=0 loops=4)
                                               Index Cond: (groupid = ANY ('{21,45}'::bigint[]))
                                         ->  Hash  (cost=1.44..1.44 rows=3 width=16) (never executed)
                                               ->  Index Only Scan using hosts_groups_1 on hosts_groups hgg  (cost=0.29..1.44 rows=3 width=16) (never executed)
                                                     Index Cond: (hostid = h.hostid)
                                                     Heap Fetches: 0
               ->  Nested Loop  (cost=1.40..290.22 rows=139 width=8) (never executed)
                     ->  Nested Loop  (cost=0.98..213.02 rows=159 width=16) (never executed)
                           ->  Index Scan using items_1 on items i  (cost=0.55..110.65 rows=186 width=16) (never executed)
                                 Index Cond: (hostid = h.hostid)
                                 Filter: (status = 0)
                           ->  Index Scan using functions_2 on functions f  (cost=0.42..0.51 rows=4 width=16) (never executed)
                                 Index Cond: (itemid = i.itemid)
                     ->  Index Scan using triggers_pkey on triggers t  (cost=0.42..0.49 rows=1 width=8) (never executed)
                           Index Cond: (triggerid = f.triggerid)
                           Filter: ((flags = ANY ('{0,4}'::integer[])) AND (status = 0))
 Planning time: 2.670 ms
 Execution time: 0.202 ms

and here the execution plan of the suggested query:

                                                                              QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=492.16..494.15 rows=199 width=30) (actual time=0.100..0.100 rows=0 loops=1)
   Group Key: h.hostid, h.name, h.status
   ->  Nested Loop  (cost=3.20..490.67 rows=199 width=30) (actual time=0.098..0.098 rows=0 loops=1)
         ->  Nested Loop  (cost=2.78..380.45 rows=227 width=38) (actual time=0.097..0.097 rows=0 loops=1)
               ->  Nested Loop  (cost=2.35..234.05 rows=266 width=38) (actual time=0.097..0.097 rows=0 loops=1)
                     Join Filter: (hg.hostid = i.hostid)
                     ->  Nested Loop  (cost=1.80..65.76 rows=2 width=38) (actual time=0.097..0.097 rows=0 loops=1)
                           ->  Index Scan using hosts_groups_2 on hosts_groups hg  (cost=0.29..8.56 rows=7 width=16) (actual time=0.010..0.013 rows=4 loops=1)
                                 Index Cond: (groupid = '174'::bigint)
                           ->  Bitmap Heap Scan on hosts h  (cost=1.52..8.16 rows=1 width=30) (actual time=0.019..0.019 rows=0 loops=4)
                                 Recheck Cond: (hostid = hg.hostid)
                                 Filter: ((flags = ANY ('{0,4}'::integer[])) AND (status = 0) AND (SubPlan 1))
                                 Rows Removed by Filter: 1
                                 Heap Blocks: exact=4
                                 ->  Bitmap Index Scan on hosts_pkey  (cost=0.00..1.52 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=4)
                                       Index Cond: (hostid = hg.hostid)
                                 SubPlan 1
                                   ->  GroupAggregate  (cost=0.28..5.53 rows=1 width=40) (actual time=0.009..0.009 rows=0 loops=4)
                                         Group Key: hg.hostid
                                         Filter: ((min(r.permission) > 0) AND (max(r.permission) >= 2))
                                         ->  Result  (cost=0.28..5.51 rows=1 width=12) (actual time=0.008..0.008 rows=0 loops=4)
                                               One-Time Filter: (h.hostid = hg.hostid)
                                               ->  Index Scan using rights_2 on rights r  (cost=0.28..5.51 rows=1 width=12) (actual time=0.008..0.008 rows=0 loops=4)
                                                     Index Cond: (id = hg.groupid)
                                                     Filter: (groupid = ANY ('{21,45}'::bigint[]))
                                                     Rows Removed by Filter: 3
                     ->  Index Scan using items_1 on items i  (cost=0.55..81.82 rows=186 width=16) (never executed)
                           Index Cond: (hostid = h.hostid)
                           Filter: (status = 0)
               ->  Index Scan using functions_2 on functions f  (cost=0.42..0.51 rows=4 width=16) (never executed)
                     Index Cond: (itemid = i.itemid)
         ->  Index Scan using triggers_pkey on triggers t  (cost=0.42..0.49 rows=1 width=8) (never executed)
               Index Cond: (triggerid = f.triggerid)
               Filter: ((flags = ANY ('{0,4}'::integer[])) AND (status = 0))
 Planning time: 3.962 ms
 Execution time: 0.214 ms
Comment by Vladislavs Sokurenko [ 2018 Jan 24 ]

Thanks okkuv9xh which one is faster for you ?

Comment by Marc [ 2018 Jan 24 ]

well, judging the numbers, the former was 1.304 ms faster then the latter.

Comment by Alexander Vladishev [ 2018 Feb 21 ]

The query is wrong. Proposed query returns inaccessible hosts.

For example:

  1. create two host groups:
    1. one with Read permissions
    2. second with Deny
  2. create host with these groups

Current SQL query:

mysql> SELECT DISTINCT h.hostid,h.name,h.status FROM hosts h,hosts_groups hg 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 ('13','14') WHERE h.hostid=hgg.hostid GROUP BY hgg.hostid HAVING MIN(r.permission)>0 AND MAX(r.permission)>='2') AND hg.groupid='17' AND hg.hostid=h.hostid AND h.status=0 AND EXISTS (SELECT NULL FROM items i,functions f,triggers t WHERE h.hostid=i.hostid AND i.itemid=f.itemid AND f.triggerid=t.triggerid AND i.status=0 AND t.status=0 AND t.flags IN (0,4));
Empty set (0.00 sec)

Proposed SQL query:

mysql> SELECT DISTINCT h.hostid,h.name,h.status FROM hosts h,hosts_groups hg , items i,functions f,triggers t WHERE h.flags IN (0,4) AND EXISTS (SELECT NULL FROM  rights r WHERE r.id=hg.groupid AND r.groupid IN ('13','14') AND h.hostid=hg.hostid GROUP BY hg.hostid HAVING MIN(r.permission)>0 AND MAX(r.permission)>='2') AND hg.groupid in('17') AND hg.hostid=h.hostid AND h.status=0  and  h.hostid=i.hostid AND i.itemid=f.itemid AND f.triggerid=t.triggerid AND i.status=0 AND t.status=0 AND t.flags IN (0,4);
+--------+-------+--------+
| hostid | name  | status |
+--------+-------+--------+
|  20254 | host  |      0 |
+--------+-------+--------+
1 row in set (0.00 sec)
Comment by Alexander Vladishev [ 2019 Jul 10 ]

Closed as Won't Fix.

Generated at Sat Aug 02 09:51:16 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.