Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-13275

Slow Housekeeping of events

XMLWordPrintable

    • Icon: Problem report Problem report
    • Resolution: Fixed
    • Icon: Critical Critical
    • 3.4.6rc1, 4.0.0alpha2, 4.0 (plan)
    • 3.4.6rc1, 4.0.0alpha1, 4.0 (plan)
    • Server (S)
    • None
    • Sprint 24
    • 1

      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)
      

            vso Vladislavs Sokurenko
            vso Vladislavs Sokurenko
            Team A
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved: