Details

    • Type: Problem report
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 3.4.6rc1, 4.0.0alpha1, 4.0 (plan)
    • Fix Version/s: 3.4.6rc1, 4.0.0alpha2, 4.0 (plan)
    • Component/s: Server (S)
    • Labels:
      None
    • 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)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                vso Vladislavs Sokurenko
                Reporter:
                vso Vladislavs Sokurenko
              • Votes:
                0 Vote for this issue
                Watchers:
                8 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: