-
Incident report
-
Resolution: Duplicate
-
Major
-
None
-
3.0.13, 3.2.10, 3.4.4
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
- duplicates
-
ZBX-11654 Performance regression in selecting host groups (after ZBX-9162)
-
- Closed
-