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

Query slow with large events table

    XMLWordPrintable

    Details

    • Type: Incident report
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 1.9.6 (beta)
    • Fix Version/s: 1.9.9 (beta)
    • Component/s: Frontend (F)
    • Environment:

      Description

      I started noticing today that the zabbix dashboard was loading slowly. In particular, the system status section took 10+ seconds to finish loading. During the loading I did a "SHOW processlist" in MySQL and found that queries like this one were running:

      SELECT e.* FROM EVENTS e
      WHERE (e.objectid IN ('16007'))
      AND ((e.objectid BETWEEN 000000000000000 AND 099999999999999))
      AND (e.object-0)=0 AND ( (e.value IN ('1'))
      AND (e.value_changed IN ('1')) )
      ORDER BY e.clock DESC,e.ns DESC LIMIT 1 OFFSET 0

      I then ran an explain on it and found that there was no index being used. My "events" table has 199.11k rows in it. Since the query had to do a table scan, it was very slow to run.

      After creating an index on the "objectid" column with the query below, the queries were MUCH faster. The "system status" section now loads up in about 2-3 seconds.
      ALTER TABLE `zabbix`.`events` ADD INDEX `events_3` (`objectid`);

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            heaje Corey Shaw
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: