-
Incident report
-
Resolution: Won't fix
-
Major
-
None
-
3.0.22
-
Production Environment, Redhat 7.2, MySQL TokuDB 5.7
Steps to reproduce:
CREATE TABLE `history_text` ( `id` bigint(20) unsigned NOT NULL, `itemid` bigint(20) unsigned NOT NULL, `clock` int(11) NOT NULL DEFAULT '0', `value` text COLLATE utf8mb4_bin NOT NULL, `ns` int(11) NOT NULL DEFAULT '0', KEY `history_text_1` (`itemid`,`clock`), KEY `history_text_2` (`itemid`,`clock`), KEY `history_textid` (`id`) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin /*!50100 PARTITION BY RANGE ( clock) (PARTITION p20160914 VALUES LESS THAN (1473868800) ENGINE = InnoDB, PARTITION p20160915 VALUES LESS THAN (1473955200) ENGINE = InnoDB, ... PARTITION p20181016 VALUES LESS THAN (1539705600) ENGINE = InnoDB)
- create an item with text, zabbix sender, and a trigger with diff() function
- use zabbix_sender to create a history record
- the slow query is "
select clock,ns,value from history_text where itemid=585716 and clock<=1538964750 and clock>1538359950 order by clock desc limit 2;
- if remove "order by clock", the query will be quick, and results 0 record.
Result:
The slow query lasts 80 seconds, but if removing the "order by" the results ends soon.
This slow query makes mysql slow, and block the history written in.
Expected: