[ZBX-6002] Slow query on srv_status.php page Created: 2012 Dec 16  Updated: 2017 May 30  Resolved: 2013 Feb 14

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Frontend (F)
Affects Version/s: 2.0.5rc1
Fix Version/s: 2.0.5rc1, 2.1.0

Type: Incident report Priority: Major
Reporter: Alexey Pustovalov Assignee: Unassigned
Resolution: Fixed Votes: 1
Labels: performance
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate

 Description   

This problem is for 2.0 branch only!

Look comment for getting more details.



 Comments   
Comment by Alexey Pustovalov [ 2012 Dec 16 ]

Function fetchLatestValues in CService class:

Index: CService.php
===================================================================
--- CService.php        (revision 32136)
+++ CService.php        (working copy)
@@ -1006,15 +1006,18 @@
        protected function fetchLatestValues(array $serviceIds, $beforeTime) {
                // the query will return the alarms with the maximum timestamp for each service
                // since multiple alarms can have the same timestamp, we only need to save the last one
-               $query = DBSelect(
-                       'SELECT sa.serviceid,sa.value
-                               FROM service_alarms sa
-                                       LEFT OUTER JOIN service_alarms sa2 ON (sa.serviceid=sa2.serviceid AND sa.clock<sa2.clock AND sa2.clock<'.zbx_dbstr($beforeTime).')
-                               WHERE sa2.servicealarmid IS NULL
-                                       AND sa.clock<'.zbx_dbstr($beforeTime).'
-                                       AND '.DBcondition('sa.serviceid', $serviceIds).'
-                               ORDER BY sa.servicealarmid'
-               );
+                $query = DBSelect(
+                       'SELECT sa.serviceid,value
+                               FROM
+                                       (SELECT MAX(sa3.servicealarmid) as servicealarmid
+                                               FROM
+                                                       (SELECT sa2.serviceid,MAX(sa2.clock) as clock from service_alarms sa2
+                                                           WHERE sa2.clock<'.zbx_dbstr($beforeTime).
+                                                           ' AND '.DBcondition('sa2.serviceid', $serviceIds).
+                                                       ' GROUP BY sa2.serviceid) AS ss
+                                               JOIN service_alarms sa3 ON sa3.serviceid = ss.serviceid and sa3.clock = ss.clock
+                                               GROUP BY sa3.serviceid) AS ss2
+                           JOIN service_alarms sa ON sa.servicealarmid = ss2.servicealarmid');

                $rs = array();
                while ($alarm = DBfetch($query)) {

Eduards RESOLVED

Comment by Nicola V [ 2012 Dec 17 ]

Hi, we are having the same issue.
I had a chat with one of our DBAs and the exact query being run is the following:

SELECT sa.serviceid,sa.value
FROM service_alarms sa
LEFT OUTER JOIN service_alarms sa2 ON (sa.serviceid=sa2.serviceid AND
sa.clock<sa2.clock AND sa2.clock<'1354886923')
WHERE sa2.servicealarmid IS NULL
AND sa.clock<'1354886923'
AND  (sa.serviceid IN
('20','17','36','8','34','14','16','37','22','7','6','35','10','23','21','19','25','24','15','1','18','13','12','33','32','3','4','5','11','9','26'))
ORDER BY sa.servicealarmid;

The postgres process shows 99% of cpu usage just after clicking on "IT Services" in the php frontend.

Zabbix version 2.0.3.

Thanks!

Comment by Alexey Pustovalov [ 2012 Dec 18 ]
Index: srv_status.php
===================================================================
--- srv_status.php      (revision 32206)
+++ srv_status.php      (working copy)
@@ -59,10 +59,9 @@
        exit();
 }

-$available_triggers = get_accessible_triggers(PERM_READ_ONLY, array());
-
 if (isset($_REQUEST['serviceid'])) {
        if ($service = DBfetch(DBselect('SELECT DISTINCT s.serviceid,s.triggerid FROM services s WHERE s.serviceid='.$_REQUEST['serviceid']))) {
+               $available_triggers = get_accessible_triggers(PERM_READ_ONLY, array());
                if ($service['triggerid'] && !isset($available_triggers[$service['triggerid']])) {
                        access_deny();
                }

Eduards RESOLVED
dotneft TESTED

Comment by Eduards Samersovs (Inactive) [ 2012 Dec 19 ]

Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-6002

Comment by Nicola V [ 2012 Dec 19 ]

Great, that was fast. Thanks

Comment by Eduards Samersovs (Inactive) [ 2012 Dec 20 ]

Fixed in versions pre-2.1.0 (beta) r32264, pre-2.0.5rc1 r32260

Comment by Oleksii Zagorskyi [ 2013 Feb 15 ]

A regression caused by this change: ZBX-6261

Generated at Thu Mar 28 12:54:59 EET 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.