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

Zabbix server generates slow "select clock,ns,value from history_uint..." queries in case of missing data for the items

    Details

    • Type: Incident report
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.4.7
    • Fix Version/s: None
    • Component/s: Server (S)
    • Environment:
      Zabbix 2.4
      MySQL 5.6/5.7

      Description

      It takes a lot of time (hours!) to get values with select clock,ns,value from history_uint where itemid=<itemid> and clock<=<timestamp> and clock><timestamp> order by clock desc limit 1 query from a big partitioned DB (e.g. ~40-50GB per each partition). These queries lead to 100% disk IO on the DB server.

      A possible solution is to replace the slow SQL query:

      select clock,ns,value from history_uint where itemid=123 and clock<=1460364250 and clock>1459759450 order by clock desc limit 1

      with these two:

      select max(clock) from history_uint where itemid=123 and clock<=1460364250 and clock>1459759450;
      select clock,ns,value from history_uint where itemid=123 and clock=1460364197 order by ns desc limit 1;

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              oleg.ivanivskyi Oleg Ivanivskyi
            • Votes:
              5 Vote for this issue
              Watchers:
              13 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: