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

Adding another index on "zabbix.history" table greatly improves performance

    Details

    • Type: Incident report
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 1.9.8 (beta)
    • Fix Version/s: 2.0.0rc3
    • Component/s: Server (S)
    • Labels:

      Description

      Hi Guys -

      As shown below, I added an index to the "history" table on the itemid, clock, and ns fields. This greatly improved performance of many of the common queries which were previously performing filesorts. One common query went from 200 seconds to <.03 seconds. There is a tradeoff that the index is on almost all of the fields. Perhaps an index-only table would help here (if they exist in MySQL).

      CREATE index history_itemid_clock_ns on history (itemid,clock,ns)

      mysql> show indexes from history ;
      ------------------------------------------------------------------------------------------------------------------------+

      Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment

      ------------------------------------------------------------------------------------------------------------------------+

      history 1 history_1 1 itemid A 17 NULL NULL   BTREE  
      history 1 history_1 2 clock A 117385964 NULL NULL   BTREE  
      history 1 history_itemid_clock_ns 1 itemid A 17 NULL NULL   BTREE  
      history 1 history_itemid_clock_ns 2 clock A 117385964 NULL NULL   BTREE  
      history 1 history_itemid_clock_ns 3 ns A 117385964 NULL NULL   BTREE  

      ------------------------------------------------------------------------------------------------------------------------+

      Before Index:
      mysql> describe select value from history where itemid=24847 and clock<1333520700 order by itemid,clock desc,ns desc limit 1;
      ---------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      ---------------------------------------------------------------------------------------------+

      1 SIMPLE history range history_1 history_1 12 NULL 420374 Using where; Using filesort

      ---------------------------------------------------------------------------------------------+

      After Index:
      mysql> describe select value from history where itemid=22922 and clock<1333520700 order by itemid,clock desc,ns desc limit 1;
      ---------------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      ---------------------------------------------------------------------------------------------------------------+

      1 SIMPLE history range history_1,history_itemid_clock_ns history_itemid_clock_ns 12 NULL 371280 Using where

      ---------------------------------------------------------------------------------------------------------------+

      And the performance gain:

      mysql> select value from history ignore index (history_itemid_clock_ns) where itemid=24847 and clock<1333520700 order by itemid,clock desc,ns desc limit 1;
      ------------

      value

      ------------

      64533.2620

      ------------
      1 row in set (3 min 22.60 sec)

      mysql> select value from history where itemid=24847 and clock<1333520700 order by itemid,clock desc,ns desc limit 1;
      ------------

      value

      ------------

      64533.2620

      ------------
      1 row in set (0.00 sec)

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              tewner Michael Tewner
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: