[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: File ZBX-13275-3.2.diff    
Issue Links:
Causes
caused by ZBX-11426 Events removed by housekeeper can cau... Closed
Duplicate
is duplicated by ZBX-13337 CLONE - Slow Housekeeping of events Closed
is duplicated by ZBX-13338 CLONE - Slow Housekeeping of events Closed
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 ZBX-12758: Upgrade notes for 3.4.0 recommend to decrease storage period for some events from 365d to 1d. Coming from Zabbix 3.2, I did that on my system. But only after doing the update, so of course the tables still contain quite many rows.

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:

  • pre-3.4.6rc1 r76462
  • pre-4.0.0alpha2 r76464
Comment by Oleksii Zagorskyi [ 2018 Feb 19 ]

Am I correct that affected version was actually 3.4.5, caused by ZBX-11426 or ZBX-12975 ?

vso updated caused by to ZBX-11426

zalex_ua heh, spot it already, thanks for update.
Just the incorrect value Affects Version/s mislead me.

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
-- User: zabbix
-- Host: localhost
-- DB: zabbix
-- Command: Query
-- Time: 1088
-- State: Sending data
{{SELECT }}
{{ MIN(clock)}}
FROM
{{ events}}
WHERE
{{ events.source = 0 AND events.object = 0}}
{{ AND NOT EXISTS( SELECT }}
{{ NULL}}
{{ FROM}}
{{ problem}}
{{ WHERE}}
{{ events.eventid = problem.eventid}}
{{ OR events.eventid = problem.r_eventid);}}

 

 

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 ZBX-13275-3.2.diff patch to 3.2 source ?

Thank you !!!!

      • What I did on a test env*

yum install svn
mkdir patch
cd patch
svn checkout svn://svn.zabbix.com/branches/3.2
svn checkout svn://svn.zabbix.com/branches/3.4

cd 3.4
svn diff -c 76462
svn diff -c 76462 > ../ZBX-13275-3.2.diff

cd ..

[patch]# ll
total 12
drwxr-xr-x 16 root root 4096 May 9 12:34 3.2
drwxr-xr-x 17 root root 4096 May 9 12:33 3.4
rw-rr- 1 root root 2529 May 9 12:33 ZBX-13275-3.2.diff

 

Generated at Sat Nov 08 11:45:20 EET 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.