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

Performance regression in selecting host groups (after ZBX-9162)

    XMLWordPrintable

    Details

    • Team:
      Team B
    • Sprint:
      Sprint 4, Sprint 5, Sprint 6, Sprint 7, Sprint 8, Sprint 9, Sprint 10, Sprint 11, Sprint 12, Sprint 13, Sprint 14, Sprint 15, Sprint 16, Sprint 17, Sprint 46, Nov 2018
    • Story Points:
      1

      Description

      Middle/big zabbix installation has been upgraded from 2.2.9 to 3.0.6 and one performance issue appeared.
      When trying to open pages on Monitoring menu (at least for Triggers, Graphs) - page appears with a big delay - 90+ seconds.
      When page is loading, such SQL is executed, its explain:

      mysql> explain SELECT g.groupid,g.name FROM groups g WHERE EXISTS  (SELECT NULL FROM hosts h,items i,graphs_items gi,graphs gr use index (PRIMARY),hosts_groups hg  WHERE hg.hostid=h.hostid AND h.status IN ('0','1')  AND hg.hostid=i.hostid AND i.itemid=gi.itemid  AND gi.graphid=gr.graphid AND gr.flags IN ('0','4')  AND g.groupid=hg.groupid) ORDER BY g.name;
      +----+--------------------+-------+--------+-------------------------------+----------------+---------+----------------+------+--------------------------+
      | id | select_type        | table | type   | possible_keys                 | key            | key_len | ref            | rows | Extra                    |
      +----+--------------------+-------+--------+-------------------------------+----------------+---------+----------------+------+--------------------------+
      |  1 | PRIMARY            | g     | index  | NULL                          | groups_1       | 194     | NULL           |    7 | Using where; Using index |
      |  2 | DEPENDENT SUBQUERY | hg    | ref    | hosts_groups_1,hosts_groups_2 | hosts_groups_2 | 8       | 3.0.g.groupid  |    5 |                          |
      |  2 | DEPENDENT SUBQUERY | h     | eq_ref | PRIMARY,hosts_2               | PRIMARY        | 8       | 3.0.hg.hostid  |    1 | Using where              |
      |  2 | DEPENDENT SUBQUERY | i     | ref    | PRIMARY,items_1               | items_1        | 8       | 3.0.hg.hostid  |    9 | Using index              |
      |  2 | DEPENDENT SUBQUERY | gi    | ref    | graphs_items_1,graphs_items_2 | graphs_items_1 | 8       | 3.0.i.itemid   |    1 |                          |
      |  2 | DEPENDENT SUBQUERY | gr    | eq_ref | PRIMARY                       | PRIMARY        | 8       | 3.0.gi.graphid |    1 | Using where              |
      +----+--------------------+-------+--------+-------------------------------+----------------+---------+----------------+------+--------------------------+
      6 rows in set (0.00 sec)
      

      it returns 304 groups.
      This SQL is for Graphs page. For triggers page its similar and also takes much time.

      There was a change ZBX-9162 (commited to 2.2.12, 3.0.2, 3.2.0), which changed considered SQL.
      Trying to understand what could be a reason of the regression I tried revision 59333 (before the fix) of svn://svn.zabbix.com/branches/3.0/frontends/php/include/classes/api/services/CHostGroup.php -r 59333 and it immediately resolved the performance regression.
      (yes, there were other further changes after r59333, but for a quick test just reverting file revision is ok).
      The older SQL takes less than a second.

      mysqld --version 
      mysqld  Ver 5.5.38-MariaDB-wsrep-log for Linux on x86_64 (MariaDB Server, wsrep_25.10.r3997)
      

      Indexes of involved tables were checked - they are the same as in official schema.

      More debug info from the PROD installation and MY TEST Debian workstation (not sure why explains are different sometimes) :

      PROD:
      MariaDB [zabbix]> explain SELECT g.groupid,g.name FROM groups g WHERE EXISTS  (SELECT NULL FROM hosts h,items i,graphs_items gi,graphs gr use index (PRIMARY),hosts_groups hg  WHERE hg.hostid=h.hostid AND h.status IN ('0','1')  AND hg.hostid=i.hostid AND i.itemid=gi.itemid  AND gi.graphid=gr.graphid AND gr.flags IN ('0','4')  AND g.groupid=hg.groupid) ORDER BY g.name;
      +------+--------------------+-------+--------+-------------------------------+----------------+---------+----------------------------------+-------+--------------------------+
      | id   | select_type        | table | type   | possible_keys                 | key            | key_len | ref                              | rows  | Extra                    |
      +------+--------------------+-------+--------+-------------------------------+----------------+---------+----------------------------------+-------+--------------------------+
      |    1 | PRIMARY            | g     | index  | NULL                          | groups_1       | 66      | NULL                             |   366 | Using where; Using index |
      |    2 | DEPENDENT SUBQUERY | gr    | ALL    | PRIMARY                       | NULL           | NULL    | NULL                             | 48442 | Using where              |
      |    2 | DEPENDENT SUBQUERY | gi    | ref    | graphs_items_1,graphs_items_2 | graphs_items_2 | 8       | zabbix.gr.graphid                |     1 |                          |
      |    2 | DEPENDENT SUBQUERY | i     | eq_ref | PRIMARY,items_1               | PRIMARY        | 8       | zabbix.gi.itemid                 |     1 |                          |
      |    2 | DEPENDENT SUBQUERY | hg    | eq_ref | hosts_groups_1,hosts_groups_2 | hosts_groups_1 | 16      | zabbix.i.hostid,zabbix.g.groupid |     1 | Using index              |
      |    2 | DEPENDENT SUBQUERY | h     | eq_ref | PRIMARY,hosts_2               | PRIMARY        | 8       | zabbix.i.hostid                  |     1 | Using where              |
      +------+--------------------+-------+--------+-------------------------------+----------------+---------+----------------------------------+-------+--------------------------+
      6 rows in set (0.00 sec)
      
      
      MY TEST:
      mysqld --version
      mysqld  Ver 5.5.53-0+deb8u1 for debian-linux-gnu on x86_64 ((Debian))
      mysql> explain SELECT g.groupid,g.name FROM groups g WHERE EXISTS  (SELECT NULL FROM hosts h,items i,graphs_items gi,graphs gr use index (PRIMARY),hosts_groups hg  WHERE hg.hostid=h.hostid AND h.status IN ('0','1')  AND hg.hostid=i.hostid AND i.itemid=gi.itemid  AND gi.graphid=gr.graphid AND gr.flags IN ('0','4')  AND g.groupid=hg.groupid) ORDER BY g.name;
      +----+--------------------+-------+--------+-------------------------------+----------------+---------+----------------+------+--------------------------+
      | id | select_type        | table | type   | possible_keys                 | key            | key_len | ref            | rows | Extra                    |
      +----+--------------------+-------+--------+-------------------------------+----------------+---------+----------------+------+--------------------------+
      |  1 | PRIMARY            | g     | index  | NULL                          | groups_1       | 194     | NULL           |    7 | Using where; Using index |
      |  2 | DEPENDENT SUBQUERY | hg    | ref    | hosts_groups_1,hosts_groups_2 | hosts_groups_2 | 8       | 3.0.g.groupid  |    5 |                          |
      |  2 | DEPENDENT SUBQUERY | h     | eq_ref | PRIMARY,hosts_2               | PRIMARY        | 8       | 3.0.hg.hostid  |    1 | Using where              |
      |  2 | DEPENDENT SUBQUERY | i     | ref    | PRIMARY,items_1               | items_1        | 8       | 3.0.hg.hostid  |    9 | Using index              |
      |  2 | DEPENDENT SUBQUERY | gi    | ref    | graphs_items_1,graphs_items_2 | graphs_items_1 | 8       | 3.0.i.itemid   |    1 |                          |
      |  2 | DEPENDENT SUBQUERY | gr    | eq_ref | PRIMARY                       | PRIMARY        | 8       | 3.0.gi.graphid |    1 | Using where              |
      +----+--------------------+-------+--------+-------------------------------+----------------+---------+----------------+------+--------------------------+
      6 rows in set (0.00 sec)
      
      

      Just in case - subquery of the SQL (without explain, on the PROD gives ~400k NULL lines in less than one second):

      PROD:
      MariaDB [zabbix]> explain SELECT NULL FROM hosts h,items i,graphs_items gi,graphs gr use index (PRIMARY),hosts_groups hg  WHERE hg.hostid=h.hostid AND h.status IN ('0','1')  AND hg.hostid=i.hostid AND i.itemid=gi.itemid  AND gi.graphid=gr.graphid AND gr.flags IN ('0','4');
      +------+-------------+-------+--------+-------------------------------+----------------+---------+-------------------+-------+-------------+
      | id   | select_type | table | type   | possible_keys                 | key            | key_len | ref               | rows  | Extra       |
      +------+-------------+-------+--------+-------------------------------+----------------+---------+-------------------+-------+-------------+
      |    1 | SIMPLE      | gr    | ALL    | PRIMARY                       | NULL           | NULL    | NULL              | 48442 | Using where |
      |    1 | SIMPLE      | gi    | ref    | graphs_items_1,graphs_items_2 | graphs_items_2 | 8       | zabbix.gr.graphid |     1 |             |
      |    1 | SIMPLE      | i     | eq_ref | PRIMARY,items_1               | PRIMARY        | 8       | zabbix.gi.itemid  |     1 |             |
      |    1 | SIMPLE      | hg    | ref    | hosts_groups_1                | hosts_groups_1 | 8       | zabbix.i.hostid   |     1 | Using index |
      |    1 | SIMPLE      | h     | eq_ref | PRIMARY,hosts_2               | PRIMARY        | 8       | zabbix.i.hostid   |     1 | Using where |
      +------+-------------+-------+--------+-------------------------------+----------------+---------+-------------------+-------+-------------+
      5 rows in set (0.00 sec)
      
      
      MY TEST:
      mysql> explain SELECT NULL FROM hosts h,items i,graphs_items gi,graphs gr use index (PRIMARY),hosts_groups hg  WHERE hg.hostid=h.hostid AND h.status IN ('0','1')  AND hg.hostid=i.hostid AND i.itemid=gi.itemid  AND gi.graphid=gr.graphid AND gr.flags IN ('0','4');
      +----+-------------+-------+--------+-------------------------------+----------------+---------+----------------+------+--------------------------+
      | id | select_type | table | type   | possible_keys                 | key            | key_len | ref            | rows | Extra                    |
      +----+-------------+-------+--------+-------------------------------+----------------+---------+----------------+------+--------------------------+
      |  1 | SIMPLE      | h     | range  | PRIMARY,hosts_2               | hosts_2        | 4       | NULL           |   11 | Using where; Using index |
      |  1 | SIMPLE      | hg    | ref    | hosts_groups_1                | hosts_groups_1 | 8       | 3.0.h.hostid   |    1 | Using index              |
      |  1 | SIMPLE      | i     | ref    | PRIMARY,items_1               | items_1        | 8       | 3.0.h.hostid   |    9 | Using index              |
      |  1 | SIMPLE      | gi    | ref    | graphs_items_1,graphs_items_2 | graphs_items_1 | 8       | 3.0.i.itemid   |    1 |                          |
      |  1 | SIMPLE      | gr    | eq_ref | PRIMARY                       | PRIMARY        | 8       | 3.0.gi.graphid |    1 | Using where              |
      +----+-------------+-------+--------+-------------------------------+----------------+---------+----------------+------+--------------------------+
      5 rows in set (0.00 sec)
      

      The older SQL:

      PROD: 
      MariaDB [zabbix]> explain SELECT  DISTINCT  g.groupid,g.name FROM groups g,hosts_groups hg,hosts h WHERE g.groupid=hg.groupid AND h.hostid=hg.hostid AND h.status IN (0,1) AND EXISTS (SELECT NULL FROM items i,graphs_items gi,graphs g WHERE hg.hostid=i.hostid AND i.itemid=gi.itemid AND gi.graphid=g.graphid AND g.flags IN (0,4)) ORDER BY g.name;
      +------+--------------------+-------+--------+-------------------------------+----------------+---------+-------------------+-------+----------------------------------------------+
      | id   | select_type        | table | type   | possible_keys                 | key            | key_len | ref               | rows  | Extra                                        |
      +------+--------------------+-------+--------+-------------------------------+----------------+---------+-------------------+-------+----------------------------------------------+
      |    1 | PRIMARY            | g     | index  | PRIMARY                       | groups_1       | 66      | NULL              |   366 | Using index; Using temporary; Using filesort |
      |    1 | PRIMARY            | hg    | ref    | hosts_groups_1,hosts_groups_2 | hosts_groups_2 | 8       | zabbix.g.groupid  |    11 | Using where; Distinct                        |
      |    1 | PRIMARY            | h     | eq_ref | PRIMARY,hosts_2               | PRIMARY        | 8       | zabbix.hg.hostid  |     1 | Using where; Distinct                        |
      |    2 | DEPENDENT SUBQUERY | i     | ref    | PRIMARY,items_1               | items_1        | 8       | zabbix.hg.hostid  | 28446 | Using index                                  |
      |    2 | DEPENDENT SUBQUERY | gi    | ref    | graphs_items_1,graphs_items_2 | graphs_items_1 | 8       | zabbix.i.itemid   |     1 |                                              |
      |    2 | DEPENDENT SUBQUERY | g     | eq_ref | PRIMARY                       | PRIMARY        | 8       | zabbix.gi.graphid |     1 | Using where                                  |
      +------+--------------------+-------+--------+-------------------------------+----------------+---------+-------------------+-------+----------------------------------------------+
      6 rows in set (0.00 sec)
      
      MY TEST:
      mysql> explain SELECT  DISTINCT  g.groupid,g.name FROM groups g,hosts_groups hg,hosts h WHERE g.groupid=hg.groupid AND h.hostid=hg.hostid AND h.status IN (0,1) AND EXISTS (SELECT NULL FROM items i,graphs_items gi,graphs g WHERE hg.hostid=i.hostid AND i.itemid=gi.itemid AND gi.graphid=g.graphid AND g.flags IN (0,4)) ORDER BY g.name;
      +----+--------------------+-------+--------+-------------------------------+----------------+---------+----------------+------+-----------------------------------------------------------+
      | id | select_type        | table | type   | possible_keys                 | key            | key_len | ref            | rows | Extra                                                     |
      +----+--------------------+-------+--------+-------------------------------+----------------+---------+----------------+------+-----------------------------------------------------------+
      |  1 | PRIMARY            | h     | range  | PRIMARY,hosts_2               | hosts_2        | 4       | NULL           |   11 | Using where; Using index; Using temporary; Using filesort |
      |  1 | PRIMARY            | hg    | ref    | hosts_groups_1,hosts_groups_2 | hosts_groups_1 | 8       | 3.0.h.hostid   |    1 | Using where; Using index                                  |
      |  1 | PRIMARY            | g     | eq_ref | PRIMARY                       | PRIMARY        | 8       | 3.0.hg.groupid |    1 |                                                           |
      |  2 | DEPENDENT SUBQUERY | i     | ref    | PRIMARY,items_1               | items_1        | 8       | 3.0.hg.hostid  |    9 | Using index                                               |
      |  2 | DEPENDENT SUBQUERY | gi    | ref    | graphs_items_1,graphs_items_2 | graphs_items_1 | 8       | 3.0.i.itemid   |    1 |                                                           |
      |  2 | DEPENDENT SUBQUERY | g     | eq_ref | PRIMARY                       | PRIMARY        | 8       | 3.0.gi.graphid |    1 | Using where                                               |
      +----+--------------------+-------+--------+-------------------------------+----------------+---------+----------------+------+-----------------------------------------------------------+
      6 rows in set (0.02 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sasha Alexander Vladishev
              Reporter:
              zalex_ua Oleksii Zagorskyi
              Votes:
              4 Vote for this issue
              Watchers:
              16 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: