[ZBX-4202] tr_status.php generates too many queries to the DB Created: 2011 Oct 04  Updated: 2017 May 30  Resolved: 2012 Mar 06

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Frontend (F)
Affects Version/s: None
Fix Version/s: 2.0.0rc1

Type: Incident report Priority: Minor
Reporter: Oleksii Zagorskyi Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: database, performance
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

zabbix php frontend >=1.8.4
Postgresql backend

Issue Links:
Duplicate

 Description   

Why for generating one page are performed 200 queries to DB?
Queries like:

"SELECT COUNT(DISTINCT e.eventid) as rowscount FROM events e WHERE (e.objectid IN (14770)) AND ((e.objectid BETWEEN 000000000000000 AND 099999999999999)) AND e.object=0 AND e.acknowledged=0 AND (e.value IN (1)) "
and
"SELECT DISTINCT h.host,t.description,t.expression,t.triggerid FROM triggers t, functions f, items i, hosts h WHERE f.triggerid=t.triggerid AND i.itemid=f.itemid AND h.hostid=i.hostid AND t.triggerid=22654".

Could be rewritten as:

"SELECT h.hostid FROM hosts h WHERE (h.hostid IN (10070,10169,10222,10229,10216,10220,10235,10733,10234,10175,10170,10362,10117,10689,10069,10745,10171,10209,10214,10204,10225,10183,10208,10211,10210,10205,10172,10173,10206,10185,10177,10176,10236,10232,10370,10212,10174,10223,10207,10219,10215)) AND ((h.hostid BETWEEN 000000000000000 AND 099999999999999)) AND h.status IN (0,1)"?

Why use "IN", if inside it is used only one value?



 Comments   
Comment by Toms (Inactive) [ 2012 Feb 23 ]

At this point multiple "SELECT COUNT(DISTINCT e.eventid) ..." and multiple user data queries resolved.
Others still to fix.

Comment by Toms (Inactive) [ 2012 Feb 24 ]

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

Comment by Toms (Inactive) [ 2012 Feb 24 ]

One query unfixed. Global structural changes should be considered.

Comment by Alexander Vladishev [ 2012 Feb 27 ]

(1) Please review my changes in r25639:25640

<Sasha> all my changes have disappeared

<Toms> My fault. I think it is due to misunderstanding with several branches. Nothing to resolve.

<Sasha> CLOSED

Comment by Alexander Vladishev [ 2012 Feb 27 ]

(2) frontends/php/api/classes/class.cevent.php:176 Here a COUNT() function should be with DISTINCT.

<Toms> Resolved in r25654
<Sasha> CLOSED

Comment by Alexander Vladishev [ 2012 Feb 27 ]

(3) frontends/php/api/classes/class.cevent.php:359 Possible undefined variable. DBselect can return NULL.

<Toms> Resolved in r25654
<Sasha> CLOSED

Comment by Alexander Vladishev [ 2012 Feb 27 ]

(4) 'groupCount' option should depend on other options. For example hostids, triggerids ...

<Toms> Resolved in r25654
<Sasha> CLOSED

Comment by Alexander Vladishev [ 2012 Feb 28 ]

(5) Please rename expand_trigger_description_multiple() function according to codung style.
http://www.zabbix.org/wiki/Docs/specs/coding_style#PHP_conventions

<Toms> Resolved in r25670
<Sasha> REOPENED function name should be like expandTriggersDescriptions()

<Toms> got the idea. RESOLVED in r25682
<Sasha> CLOSED

Comment by Alexander Vladishev [ 2012 Feb 28 ]

(6) A new function DBfetchFirstArray() isn't used anywhere

<Toms> Resolved in r25670
<Sasha> CLOSED

Comment by Alexander Vladishev [ 2012 Feb 28 ]

(7) Please recreate development branch. ZBX-4202-2 has been created from ZBX-4202. Not from trunk!

<Toms> Resolved in r25670
<Sasha> CLOSED

Comment by Alexander Vladishev [ 2012 Feb 29 ]

(8) frontends/php/tr_status.php:447 unused code should not be commented out, but removed.

<Toms> RESOLVED in r25682
<Sasha> CLOSED

Comment by Alexander Vladishev [ 2012 Feb 29 ]

(9) new or changed lines should be formatted correctly:
overview.php:132 no spaces between function arguments
triggers.inc.php:1311:1313 extra space between sql conditions
class.cuser.php:1010 the following statement(s) after if, while, for ... should always be in braces
class.cevent.php:365 "else {" statement should be on a new line
tr_status.php:372,376,379,443,451,530,532

<Toms> RESOLVED in r25682
<Sasha> CLOSED

Comment by Alexander Vladishev [ 2012 Mar 05 ]

(10) Long SQL. Please look at it.

Time:2.283103 LONG SQL: SELECT DISTINCT t.*,hg.groupid FROM triggers t,functions f,items i,hosts_groups hg WHERE t.triggerid BETWEEN 000000000000000 AND 099999999999999 AND hg.hostid=i.hostid AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND (hg.groupid IN ('6')) AND NOT EXISTS ( SELECT ff.functionid FROM functions ff WHERE ff.triggerid=t.triggerid AND EXISTS ( SELECT ii.itemid FROM items ii,hosts hh WHERE ff.itemid=ii.itemid AND hh.hostid=ii.hostid AND ( ii.status<>0 OR hh.status<>0 ) ) ) AND t.status=0 AND ( (t.flags IN ('0','4')) ) ORDER BY lastchange DESC,t.lastchange DESC
CAPIObject->get() > CAPIObject>__call() > czbxrpc::call() -> czbxrpc::callAPI() -> call_user_func() -> CTrigger>get() -> DBselect()

<Toms> RESOLVED in r.25812
<Sasha> CLOSED Now: Time:1.03052 ....

Comment by Alexander Vladishev [ 2012 Mar 05 ]

(11) The trigger dependencies aren't shown correctly

<Toms> RESOLVED in r.25812
<Sasha> CLOSED

Comment by Alexander Vladishev [ 2012 Mar 05 ]

(12) Please review my changes in r25785:25786.

<Toms> RESOLVED in r.25812
<Sasha> CLOSED

Comment by Alexander Vladishev [ 2012 Mar 05 ]

(13) Undefined variable: description [tr_status.php:478]

<Toms> RESOLVED in r.25812
<Sasha> CLOSED

Comment by Toms (Inactive) [ 2012 Mar 06 ]

..F....... ZBX-4202 Optimized triggerids retrieval. Fixed simple bugs.

Comment by Toms (Inactive) [ 2012 Mar 06 ]

Resolved in r.25831

Generated at Thu Apr 25 16:58:03 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.