-
Problem report
-
Resolution: Won't fix
-
Major
-
None
-
4.2.6
-
Team B
-
Sprint 57 (Oct 2019), Sprint 58 (Nov 2019), Sprint 59 (Dec 2019), Sprint 60 (Jan 2020), Sprint 61 (Feb 2020), Sprint 62 (Mar 2020), Sprint 63 (Apr 2020), Sprint 64 (May 2020), Sprint 65 (Jun 2020), Sprint 66 (Jul 2020)
We suggest to optimize SQLs, where permission is checked:
For example:
SELECT * FROM (SELECT DISTINCT p.eventid,p.objectid,p.clock,p.ns,p.name,p.severity FROM problem p,functions f,items i WHERE p.source='0' AND p.object='0' AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN (18,28,44,62,76,120,148) WHERE p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid IN (21496,21501,21506,21507,21508,21509,21510,21511,21521,21524,21528,21531,21907,30532,31249,31250,32555,32679,32680,33533,33534,33535,33536,33537,33538,33539,34167,34683,36197,36198,36199,36200,42649) AND p.severity IN (3,4) AND p.r_eventid IS NULL ORDER BY p.eventid DESC) WHERE rownum BETWEEN 0 AND 501
this SQL takes more than a minute and on bit sets od data - even longer, 5-10 minutes
Optimized version:
SELECT *
FROM (
SELECT DISTINCT p.eventid,
p.objectid,
p.clock,
p.ns,
p.name,
p.severity
FROM
problem p
inner join functions f on
p.objectid = f.triggerid
and p.source = 0
and p.object = 0
inner join items i on
f.itemid = i.itemid
AND i.hostid IN (21496,21501,21506,21507,21508,21509,21510,21511,21521,21524,21528,21531,21907,30532,31249,31250,32555,32679,32680,33533,33534,33535,33536,33537,33538,33539,34167,34683,36197,36198,36199,36200,42649)
AND p.severity IN (3, 4)
AND p.r_eventid IS NULL
WHERE
NOT EXISTS (
SELECT
null
FROM
functions f
inner join items i on f.itemid = i.itemid
inner join hosts_groups hgg on i.hostid = hgg.hostid
LEFT JOIN rights r ON r.id = hgg.groupid AND r.groupid IN (18, 28, 44, 62, 76, 120, 148)
WHERE
p.objectid = f.triggerid
and nvl(r.permission,0) = 0
)
and exists (
SELECT
null
FROM
functions f
inner join items i on f.itemid = i.itemid
inner join hosts_groups hgg on i.hostid = hgg.hostid
LEFT JOIN rights r ON r.id = hgg.groupid AND r.groupid IN (18, 28, 44, 62, 76, 120, 148)
WHERE
p.objectid = f.triggerid
and nvl(r.permission,0) in (2,3)
)
ORDER BY p.eventid DESC
)
WHERE
rownum BETWEEN 0 AND 501
What is optimized:
In original query, this block:
AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN (18,28,44,62,76,120,148) WHERE p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0)
replace by:
NOT EXISTS (
SELECT
null
FROM
functions f
inner join items i on f.itemid = i.itemid
inner join hosts_groups hgg on i.hostid = hgg.hostid
LEFT JOIN rights r ON r.id = hgg.groupid AND r.groupid IN (18, 28, 44, 62, 76, 120, 148)
WHERE
p.objectid = f.triggerid
and nvl(r.permission,0) = 0
)
and exists (
SELECT
null
FROM
functions f
inner join items i on f.itemid = i.itemid
inner join hosts_groups hgg on i.hostid = hgg.hostid
LEFT JOIN rights r ON r.id = hgg.groupid AND r.groupid IN (18, 28, 44, 62, 76, 120, 148)
WHERE
p.objectid = f.triggerid
and nvl(r.permission,0) in (2,3)
)
This change decreased time to 5 seconds.
We use Oracle as zabbix db.
- mentioned in
-
Page Loading...
