[ZBX-13275] Slow Housekeeping of events Created: 2017 Dec 29 Updated: 2024 Apr 10 Resolved: 2018 Jan 02 |
|
Status: | Closed |
Project: | ZABBIX BUGS AND ISSUES |
Component/s: | Server (S) |
Affects Version/s: | 3.4.6rc1, 4.0.0alpha1, 4.0 (plan) |
Fix Version/s: | 3.4.6rc1, 4.0.0alpha2, 4.0 (plan) |
Type: | Problem report | Priority: | Critical |
Reporter: | Vladislavs Sokurenko | Assignee: | Vladislavs Sokurenko |
Resolution: | Fixed | Votes: | 0 |
Labels: | None | ||
Remaining Estimate: | Not Specified | ||
Time Spent: | Not Specified | ||
Original Estimate: | Not Specified |
Attachments: | ZBX-13275-3.2.diff | ||||||||||||||||||||
Issue Links: |
|
||||||||||||||||||||
Team: | Team A | ||||||||||||||||||||
Sprint: | Sprint 24 | ||||||||||||||||||||
Story Points: | 1 |
Description |
Housekeeping of events is too slow on MySQL because it's not using index provided, the query is: select min(clock) from events where events.source=3 and events.object=0 and not exists (select null from problem where events.eventid=problem.eventid or events.eventid=problem.r_eventid) MySQL is "Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapper", running on Ubuntu 16.04. mysql> explain select min(clock) from events where events.source=3 and events.object=0 and not exists (select null from problem where events.eventid=problem.eventid or events.eventid=problem.r_eventid)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: events partitions: NULL type: ref possible_keys: events_1,events_2 key: events_2 key_len: 8 ref: const,const rows: 15964577 filtered: 100.00 Extra: Using where; Using index *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: problem partitions: NULL type: ALL possible_keys: PRIMARY,c_problem_2 key: NULL key_len: NULL ref: NULL rows: 708846 filtered: 19.00 Extra: Range checked for each record (index map: 0x9) 2 rows in set, 3 warnings (0,00 sec) It should be changed to: mysql> explain select min(clock) from events where events.source=3 and events.object=0 and not exists (select null from problem where events.eventid=problem.eventid) and not exists (select null from problem where events.eventid=problem.r_eventid)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: events partitions: NULL type: ref possible_keys: events_1,events_2 key: events_2 key_len: 8 ref: const,const rows: 15973658 filtered: 100.00 Extra: Using where; Using index *************************** 2. row *************************** id: 3 select_type: DEPENDENT SUBQUERY table: problem partitions: NULL type: ref possible_keys: c_problem_2 key: c_problem_2 key_len: 9 ref: zabbix.events.eventid rows: 1 filtered: 100.00 Extra: Using index *************************** 3. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: problem partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: zabbix.events.eventid rows: 1 filtered: 100.00 Extra: Using index 3 rows in set, 3 warnings (0,00 sec) |
Comments |
Comment by Ronald Schaten [ 2017 Dec 29 ] |
Thanks for fixing this problem. How am I supposed to deal with this? Disable housekeeping of events and alerts altogether until the next version is released? |
Comment by Vladislavs Sokurenko [ 2017 Dec 29 ] |
You can disable housekeeping before this is fixed, but why do you have 131 million of events ? Do you really need that much ? |
Comment by Ronald Schaten [ 2017 Dec 29 ] |
I don't think that I need that much. But as I mentioned in Could it be a solution to manually delete entries in events / problem that are older than a few weeks? I'm pretty sure that I wouldn't need those anymore... and that way I wouldn't need to remember activating the housekeeping after the next release. |
Comment by Andris Zeila [ 2018 Jan 02 ] |
Successfully tested. |
Comment by Vladislavs Sokurenko [ 2018 Jan 02 ] |
Fixed in:
|
Comment by Oleksii Zagorskyi [ 2018 Feb 19 ] |
Am I correct that affected version was actually 3.4.5, caused by vso updated caused by to zalex_ua heh, spot it already, thanks for update. |
Comment by Thiago M Zerbinato [ 2018 May 09 ] |
Hi Guys, I have the exact same error on 3.2.11, how can I fix it ? Can I change the query on source ? What file should I change ? Thank you ! -- Connection Id: 2262916
|
Comment by Vladislavs Sokurenko [ 2018 May 09 ] |
It's committed under revision 76462, so if you checkout svn://svn.zabbix.com/branches/3.4 and do svn diff -c 76462 you should see the changes. And command below should create a required patch: svn diff -c 76462 > ZBX-13275-3.2.diff |
Comment by Thiago M Zerbinato [ 2018 May 09 ] |
Vladislavs, How to apply the Thank you !!!!
yum install svn cd 3.4 cd .. [patch]# ll
|