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

events or event_recovery table needs an index or query hint?

    XMLWordPrintable

Details

    • Incident report
    • Status: Closed
    • Trivial
    • Resolution: Won't fix
    • 3.4.13
    • None
    • Frontend (F)
    • None
    • zabbix 3.4.13
      mysql 5.6

    Description

      Steps to reproduce:

      1. Dashboard -> Monitoring -> Problems  - every time refreshes, 1 cpu of 4 is pegged for 37 seconds.   
      2. This was an upgrade from 2.4.2 to 3.4.13
      3. is there an index I could create to make this useable?   We had 180day of events, triggers, etc. 

       

      ls -alh event*
      -rw-rw---- 1 mysql mysql 8.6K Sep 11 19:10 event_recovery.frm
      -rw-rw---- 1 mysql mysql 2.8G Sep 11 22:56 event_recovery.ibd
      -rw-rw---- 1 mysql mysql 8.7K Sep 11 18:07 events.frm
      -rw-rw---- 1 mysql mysql 11G Sep 11 22:56 events.ibd
      -rw-rw---- 1 mysql mysql 8.5K Sep 11 18:39 event_tag.frm
      -rw-rw---- 1 mysql mysql 112K Sep 11 18:39 event_tag.ibd

      innodb_buffer_pool_size=10G

      entry from slow_query log

      # Time: 180911 22:56:06
      # [email protected]: zabbixweb[zabbixweb] @ [10.39.110.78] Id: 11312
      # Schema: zabbix Last_errno: 0 Killed: 0
      # Query_time: 37.873287 Lock_time: 0.000074 Rows_sent: 20 Rows_examined: 79224127 Rows_affected: 0
      # Bytes_sent: 1300 Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 0
      # InnoDB_trx_id: 77DC9
      # QC_Hit: No Full_scan: No Full_join: No Tmp_table: Yes Tmp_table_on_disk: No
      # Filesort: No Filesort_on_disk: No Merge_passes: 0
      # InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000
      # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000
      # InnoDB_pages_distinct: 65169
      SET timestamp=1536706566;
      SELECT DISTINCT e.eventid,e.clock,e.ns,e.objectid,er1.r_eventid FROM events e LEFT JOIN event_recovery er1 ON er1.eventid=e.eventid WHERE e.source='0' AND e.object='0' AND e.objectid='28400' AND e.eventid<='103181557' AND e.value='1' ORDER BY e.eventid DESC LIMIT 20;

      Attachments

        Activity

          People

            Unassigned Unassigned
            bunkzilla bunkzilla
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: