[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: |
|
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. 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(); } |
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: |