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

Allow database to use indexes reduces database load a lot

    XMLWordPrintable

Details

    • Incident report
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 1.8.5
    • 1.8.8
    • Server (S)
    • Following patch is only tested on MySQL but should work on all databases as the SQL query is quite simple.

    Description

      The Zabbix Server sends a lot of queries like this to the database:
      SELECT value FROM history_text WHERE itemid =33839 ORDER BY id DESC LIMIT 1;

      There can be a lot of rows for one specific itemid in the history table. MySQL has to collect them all, sort them and return one single value. This is how MySQL does this job:
      mysql> explain SELECT value FROM history_text WHERE itemid =33839 ORDER BY id DESC LIMIT 1;
      ----------------------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

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

      1 SIMPLE history_text ref history_text_2,history_text_1 history_text_1 8 const 24552 Using where; Using filesort

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

      The attached patch uses additional informations from the items table to help the database to use the index efficently. This is how this query looks with the patch applied:
      SELECT h.value FROM history_text as h, items as i WHERE i.itemid=33839 and h.itemid=i.itemid and h.clock=i.lastclock ORDER BY id DESC LIMIT 1;

      And how MySQL does it's job:
      mysql> explain SELECT h.value FROM history_text as h, items as i WHERE i.itemid=33839 and h.itemid=i.itemid and h.clock=i.lastclock ORDER BY id DESC LIMIT 1;
      --------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

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

      1 SIMPLE i const PRIMARY PRIMARY 8 const 1 Using filesort
      1 SIMPLE h ref history_text_2,history_text_1 history_text_1 12 const,const 1 Using where

      --------------------------------------------------------------------------------------------------------+
      2 rows in set (0.00 sec)

      MySQL needs one more step but had only one row in each step. So it's no longer sorting 24552 rows but doing two index lookups which is much more efficient.

      It's a bit different in src/libs/zbxserver/expression.c as there are two separate queries. I modified the first one to also return the lastclock field from items and used this value in the second query to allow an index lookup here too.

      With this patch I reduced the CPU load of mysqld from about 400% to 20% on a six-core machine. (140 monitored hosts, 10k items, 200vps)

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              thtux Thomas Lohmüller
              Votes:
              9 Vote for this issue
              Watchers:
              11 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: