[ZBX-7105] very slow query in housekeeper Created: 2013 Oct 07  Updated: 2017 May 30  Resolved: 2013 Oct 23

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: 2.1.7
Fix Version/s: 2.1.9

Type: Incident report Priority: Critical
Reporter: richlv Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: housekeeper, performance, regression
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified


 Description   

new housekeeper is much more flexible, but it does some selects against events table that are very slow :

select min(clock) from events where source=1

on a not-too-fast test system with 11 million events this query took 2 hours.

mysql> explain extended select min(clock) from events where source=1;
------------------------------------------------------------------------------

id select_type table type possible_keys key key_len ref rows filtered Extra

------------------------------------------------------------------------------

1 SIMPLE events ref events_1 events_1 4 const 5593118 100.00  

------------------------------------------------------------------------------



 Comments   
Comment by Alexander Vladishev [ 2013 Oct 07 ]

Related issues: ZBX-7103, ZBX-6869, ZBX-6763

Comment by Alexander Vladishev [ 2013 Oct 16 ]

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

Comment by richlv [ 2013 Oct 16 ]

(1) we'll have to describe this in whatsnew, and it would be helpful in general to know what was changed
my guess :

a) patches 2010041 and 2010042 have been removed (they removed & recreated events_1 index);
b) both events indexes have been redone :
events_1 - source,object,objectid,clock
events_2 - source,clock
c) some frontend pages have been changed, so i assume performance in those has been improved ?

  • monitoring -> triggers
  • monitoring -> events
  • monitoring -> events -> event details

sasha Performance of these pages are not improved. Was changed only order:

  • before the change:
    all events was sorted by eventid
  • after the change:
    all events are sorted by clock field

sasha CLOSED. There is nothing to describe in "what's new".

Comment by Andris Zeila [ 2013 Oct 21 ]

[IS] server side tested

sasha The second index was changed in r39483. Please retest the server side.

Comment by Pavels Jelisejevs (Inactive) [ 2013 Oct 22 ]

(2) I suggest we add a source,object,clock index. It may be used by the API.

sasha RESOLVED in r39483.

wiper Database upgrade patch and housekeeper changes reviewed and tested.
CLOSED

Comment by Pavels Jelisejevs (Inactive) [ 2013 Oct 22 ]

(3) I've made some changes in r39485, please review.

sasha REVIEWED

Please review my changes in r39506.

jelisejev CLOSED.

Comment by Pavels Jelisejevs (Inactive) [ 2013 Oct 22 ]

(4) The object and objectid sort columns must be restored.

sasha RESOLVED in r39507 and r39521.

jelisejev CLOSED.

Comment by Pavels Jelisejevs (Inactive) [ 2013 Oct 23 ]

(5) The parameters and examples of event.get need to be updated in https://www.zabbix.com/documentation/2.2/manual/api/reference/event/get

sasha RESOLVED

Also updated:

jelisejev CLOSED.

Comment by Pavels Jelisejevs (Inactive) [ 2013 Oct 23 ]

(6) I've fixed ZBX-6389 (7) in r39520.

sasha CLOSED

Comment by Pavels Jelisejevs (Inactive) [ 2013 Oct 24 ]

Frontend TESTED.

Comment by Alexander Vladishev [ 2013 Oct 24 ]

Fixed in version pre-2.1.9 r39547

Generated at Fri Mar 29 16:12:21 EET 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.