[ZBX-15803] Frontend query runs much faster, if changed Created: 2019 Mar 12  Updated: 2024 Apr 10  Resolved: 2020 Nov 03

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Frontend (F)
Affects Version/s: 4.0.5
Fix Version/s: 4.4 (plan)

Type: Incident report Priority: Major
Reporter: Edgars Melveris Assignee: Miks Kronkalns
Resolution: Won't fix Votes: 4
Labels: bug
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Percona MySql 8.0.13 on server with 20 cores and 32GB ram

Attachments: File CAction.php     File CAction.php.org    
Issue Links:
Causes
Duplicate
Team: Team B
Sprint: Sprint 56 (Sep 2019), Sprint 55 (Aug 2019), Sprint 52 (May 2019), Sprint 53 (Jun 2019), Sprint 54 (Jul 2019), 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), Sprint 67 (Aug 2020), Sprint 68 (Sep 2020), Sprint 69 (Oct 2020), Sprint 70 (Nov 2020)

 Description    
SELECT DISTINCT p.eventid,p.objectid,p.clock,p.ns,p.name,p.severity 
FROM problem p,functions f,items i,hosts_groups hg 
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 ('14','30') 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=hg.hostid 
AND hg.groupid='68' 
AND NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=p.eventid) 
AND (p.r_eventid IS NULL OR p.r_clock>1552377061) 
AND p.eventid<='5404925' 
ORDER BY p.eventid DESC LIMIT 101

15,631 sec.
4,617 sec.
6,256 sec.
6,583 sec.
12,277 sec.
00:01:04.7
38,923 sec.
5,304 sec.
33,806 sec.
38,486 sec.

SELECT *
FROM (
SELECT DISTINCT p.eventid,p.objectid,p.clock,p.ns,p.name,p.severity 
FROM problem p,functions f,items i,hosts_groups hg 
WHERE p.source='0' 
AND p.object='0' 
AND p.objectid=f.triggerid 
AND f.itemid=i.itemid 
AND i.hostid=hg.hostid 
AND hg.groupid='68' 
AND NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=p.eventid) AND (p.r_eventid IS NULL OR p.r_clock>1552377061) AND p.eventid<='5404925' ) a
WHERE 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 ('14','30') WHERE a.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) 
ORDER BY a.eventid DESC LIMIT 101

1,435 sec.
1,779 sec.
1,592 sec.
1,826 sec.
1,482 sec
1,638 sec.
1,419 sec.
2,091 sec.
1,903 sec.

 Comments   
Comment by Kevin Daudt [ 2019 Nov 21 ]

Since our users started using more dashboards, the load on our database has increased a lot during working hours. One of the queries we see constantly in the processlist is the one in this post:

+------------------------------------------------------------------------------------------------------+--------+
| query                                                                                                | amount |
+------------------------------------------------------------------------------------------------------+--------+
| SELECT DISTINCT p.eventid,p.objectid,p.clock,p.ns,p.name,p.severity FROM problem p,functions f,items |      8 |
+------------------------------------------------------------------------------------------------------+--------+

So improvements here are more than welcome.

Comment by Matthew Steeves [ 2020 Nov 03 ]

Hey, was following this issue and saw it's been closed as "Won't fix", but no rationale provided. Could someone comment on why it's not worth fixing or why it's no longer an issue?

Thanks!

Generated at Sun Apr 06 12:52:50 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.