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

Missing index in history_uint table slows zabbix server to a complete stop.

    XMLWordPrintable

Details

    • Incident report
    • Status: Closed
    • Minor
    • Resolution: Duplicate
    • 2.2.6
    • None
    • Server (S)
    • Linux (Ubuntu 14.04), installed official deb packages from zabbix.org.

    Description

      After upgrading to Zabbix 2.2.6 all my proxies and agents were unable to connect with the zabbix server. Restarting the server would allow them to connect for a time but then connections would start failing with error like:

      • sending heartbeat message to server failed: error:"no response: network error"
      • Error while sending data to the server [ZBX_TCP_WRITE() failed: [104] Connection reset by peer
      • Unable to connect to the server Interrupted system call

      Looking at the database I could see it was at 100% IOWAIT and enabling detailed logs showed messages like:

      19875:20140909:212814.403 slow query: 301.096514 sec, "select clock,ns,value from history_uint where itemid=32136 and clock<=1406978593 order by clock desc,ns desc limit 2"
      19872:20140909:212814.409 slow query: 297.448818 sec, "select clock,ns,value from history_uint where itemid=32139 and clock<=1406978596 order by clock desc,ns desc limit 2"
      19873:20140909:212814.412 slow query: 299.953487 sec, "select clock,ns,value from history_uint where itemid=32137 and clock<=1406978594 order by clock desc,ns desc limit 2"
      19874:20140909:212814.412 slow query: 296.408654 sec, "select clock,ns,value from history_uint where itemid=32345 and clock<=1406978597 order by clock desc,ns desc limit 1"
      19876:20140909:212948.191 slow query: 396.045954 sec, "select clock,ns,value from history_uint where itemid=32135 and clock<=1406978592 order by clock desc,ns desc limit 2"
      19875:20140909:213420.069 slow query: 365.647917 sec, "select clock,ns,value from history_uint where itemid=32140 and clock<=1406978894 order by clock desc,ns desc limit 2"
      19874:20140909:213420.069 slow query: 365.622908 sec, "select clock,ns,value from history_uint where itemid=32333 and clock<=1406978597 order by clock desc,ns desc limit 1"
      19872:20140909:213420.071 slow query: 364.579979 sec, "select clock,ns,value from history_uint where itemid=32143 and clock<=1406978895 order by clock desc,ns desc limit 2"
      19873:20140909:213420.074 slow query: 365.409093 sec, "select clock,ns,value from history_uint where itemid=32142 and clock<=1406978894 order by clock desc,ns desc limit 2"
      19876:20140909:213450.130 slow query: 301.858450 sec, "select clock,ns,value from history_uint where itemid=32114 and clock<=1406978988 order by clock desc,ns desc limit 2"

      That queries was taking around 5 minutes to complete and crawling the whole zabbix to an stop.

      The problem with the query is the sorting by "ns desc" as this is not indexed. After adding an index with all three colums everything got snappy and working again:

      create index history_uint_2 on history_uint (itemid, clock, ns)

      Maybe the auto-upgrading of the database is missing this key creation?

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              hsanson Horacio
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: