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

Monitoring -> Triggers page is very slow on MariaDB >= 5.3.3

    XMLWordPrintable

    Details

      Description

      I tested many new switches, trying to OFF them, in order how mariadb shows them. An optimizer switch, which did the trick is "semijoin" https://mariadb.com/kb/en/library/semi-join-subquery-optimizations/
      If set it to "off", then this query runs very fast.

      Workaround is to add a line:

      optimizer_switch = 'semijoin=off'
      

      to my.cnf, restart mysql.
      It has been changed to ON from OFF in Maria DB 5.3.3 https://mariadb.com/kb/en/library/optimizer-switch/

      > EXPLAIN SELECT g.groupid,g.name FROM groups g WHERE EXISTS (SELECT NULL FROM hosts h,items i,functions f,triggers t,hosts_groups hg WHERE hg.hostid=h.hostid AND h.status='0' AND hg.hostid=i.hostid AND i.itemid=f.itemid AND f.triggerid=t.triggerid AND i.status='0' AND t.status='0' AND t.flags IN ('0','4') AND g.groupid=hg.groupid);
      +------+--------------+-------------+--------+-------------------------------+----------------+---------+-----------------+------+-------------+
      | id   | select_type  | table       | type   | possible_keys                 | key            | key_len | ref             | rows | Extra       |
      +------+--------------+-------------+--------+-------------------------------+----------------+---------+-----------------+------+-------------+
      |    1 | PRIMARY      | g           | index  | PRIMARY                       | groups_1       | 257     | NULL            |  678 | Using index |
      |    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key                  | distinct_key   | 8       | func            |    1 |             |
      |    2 | MATERIALIZED | h           | ref    | PRIMARY,hosts_2               | hosts_2        | 4       | const           | 4147 | Using index |
      |    2 | MATERIALIZED | hg          | ref    | hosts_groups_1,hosts_groups_2 | hosts_groups_1 | 8       | 34t.h.hostid    |    1 | Using index |
      |    2 | MATERIALIZED | i           | ref    | PRIMARY,items_1,items_3       | items_1        | 8       | 34t.h.hostid    |   46 | Using where |
      |    2 | MATERIALIZED | f           | ref    | functions_1,functions_2       | functions_2    | 8       | 34t.i.itemid    |    1 |             |
      |    2 | MATERIALIZED | t           | eq_ref | PRIMARY,triggers_1            | PRIMARY        | 8       | 34t.f.triggerid |    1 | Using where |
      +------+--------------+-------------+--------+-------------------------------+----------------+---------+-----------------+------+-------------+
      

      and the query takes 30 seconds to be done.

      > SET session optimizer_switch="semijoin=off";
      Query OK, 0 rows affected (0.00 sec)
      
      
      > EXPLAIN SELECT g.groupid,g.name FROM groups g WHERE EXISTS (SELECT NULL FROM hosts h,items i,functions f,triggers t,hosts_groups hg WHERE hg.hostid=h.hostid AND h.status='0' AND hg.hostid=i.hostid AND i.itemid=f.itemid AND f.triggerid=t.triggerid AND i.status='0' AND t.status='0' AND t.flags IN ('0','4') AND g.groupid=hg.groupid);
      +------+--------------------+-------+--------+-----------------------------------------------------------+----------------+---------+-----------------+------+--------------------------+
      | id   | select_type        | table | type   | possible_keys                                             | key            | key_len | ref             | rows | Extra                    |
      +------+--------------------+-------+--------+-----------------------------------------------------------+----------------+---------+-----------------+------+--------------------------+
      |    1 | PRIMARY            | g     | index  | NULL                                                      | groups_1       | 257     | NULL            |  678 | Using where; Using index |
      |    2 | DEPENDENT SUBQUERY | hg    | ref    | hosts_groups_1,hosts_groups_2                             | hosts_groups_2 | 8       | func            |    6 |                          |
      |    2 | DEPENDENT SUBQUERY | h     | eq_ref | PRIMARY,hosts_1,hosts_2,hosts_3,hosts_4,hosts_5,c_hosts_3 | PRIMARY        | 8       | 34t.hg.hostid   |    1 | Using where              |
      |    2 | DEPENDENT SUBQUERY | i     | ref    | PRIMARY,items_1,items_3,items_4,items_5,items_6,items_7   | items_1        | 8       | 34t.hg.hostid   |   46 | Using where              |
      |    2 | DEPENDENT SUBQUERY | f     | ref    | functions_1,functions_2                                   | functions_2    | 8       | 34t.i.itemid    |    1 |                          |
      |    2 | DEPENDENT SUBQUERY | t     | eq_ref | PRIMARY,triggers_1,triggers_3,triggers_2                  | PRIMARY        | 8       | 34t.f.triggerid |    1 | Using where              |
      +------+--------------------+-------+--------+-----------------------------------------------------------+----------------+---------+-----------------+------+--------------------------+
      

      and it runs fast as it should, like 0,1 sec.
      The SQL returns 523 rows.

      As many zabbix users are using Maria DB, it should evaluated what to do with this.

      Status of zabbix:

      Parameter						Value	Details
      Zabbix server is running				Yes	localhost:10051
      Number of hosts (enabled/disabled/templates)		4301	4134 / 4 / 163
      Number of items (enabled/disabled/not supported)	2104079	1850794 / 170175 / 83110
      Number of triggers (enabled/disabled [problem/ok])	818300	718943 / 99357 [2068 / 716875]
      Number of users (online)				138	19
      Required server performance, new values per second	6810.75
      

      The SQL is basically the same for versions 3.0-3.4

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              zalex_ua Oleksii Zagorskyi
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: