-
Incident report
-
Resolution: Won't fix
-
Trivial
-
None
-
None
-
None
-
None
Zabbix MySQL 5.7 database load goes high, that makes Zabbix unusable.
The version of Zabbix is 3.0.1.
So many queries running at the same time and when we try to run them manually they don't return any data. The table is partitioned.
We don't know which Zabbix functionality causes this and how to fix this.
Please help.
The is a query:
select clock,ns,value from history_uint where itemid=629066 and clock<=1458771019 and clock>1458166219 order by clock desc limit 100; Empty set (58.62 sec)
show create table history_uint; history_uint | CREATE TABLE `history_uint` ( `itemid` bigint(20) unsigned NOT NULL, `clock` int(11) NOT NULL DEFAULT '0', `value` bigint(20) unsigned NOT NULL DEFAULT '0', `ns` int(11) NOT NULL DEFAULT '0', KEY `history_uint_1` (`itemid`,`clock`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE ( clock) (PARTITION p2016_03_05 VALUES LESS THAN (1457222400) ENGINE = InnoDB, PARTITION p2016_03_06 VALUES LESS THAN (1457308800) ENGINE = InnoDB, PARTITION p2016_03_07 VALUES LESS THAN (1457395200) ENGINE = InnoDB, PARTITION p2016_03_08 VALUES LESS THAN (1457481600) ENGINE = InnoDB, PARTITION p2016_03_09 VALUES LESS THAN (1457568000) ENGINE = InnoDB, PARTITION p2016_03_10 VALUES LESS THAN (1457654400) ENGINE = InnoDB, PARTITION p2016_03_11 VALUES LESS THAN (1457740800) ENGINE = InnoDB, PARTITION p2016_03_12 VALUES LESS THAN (1457827200) ENGINE = InnoDB, PARTITION p2016_03_13 VALUES LESS THAN (1457913600) ENGINE = InnoDB, PARTITION p2016_03_14 VALUES LESS THAN (1458000000) ENGINE = InnoDB, PARTITION p2016_03_15 VALUES LESS THAN (1458086400) ENGINE = InnoDB, PARTITION p2016_03_16 VALUES LESS THAN (1458172800) ENGINE = InnoDB, PARTITION p2016_03_17 VALUES LESS THAN (1458259200) ENGINE = InnoDB, PARTITION p2016_03_18 VALUES LESS THAN (1458345600) ENGINE = InnoDB, PARTITION p2016_03_19 VALUES LESS THAN (1458432000) ENGINE = InnoDB, PARTITION p2016_03_20 VALUES LESS THAN (1458518400) ENGINE = InnoDB, PARTITION p2016_03_21 VALUES LESS THAN (1458604800) ENGINE = InnoDB, PARTITION p2016_03_23 VALUES LESS THAN (1458777600) ENGINE = InnoDB, PARTITION p2016_03_25 VALUES LESS THAN (1458950400) ENGINE = InnoDB, PARTITION p2016_03_26 VALUES LESS THAN (1459036800) ENGINE = InnoDB, PARTITION p2016_03_27 VALUES LESS THAN (1459123200) ENGINE = InnoDB, PARTITION p2016_03_28 VALUES LESS THAN (1459209600) ENGINE = InnoDB, PARTITION p2016_03_29 VALUES LESS THAN (1459296000) ENGINE = InnoDB, PARTITION p2016_03_30 VALUES LESS THAN (1459382400) ENGINE = InnoDB, PARTITION p2016_03_31 VALUES LESS THAN (1459468800) ENGINE = InnoDB, PARTITION p2016_04_01 VALUES LESS THAN (1459555200) ENGINE = InnoDB, PARTITION p2016_04_02 VALUES LESS THAN (1459641600) ENGINE = InnoDB, PARTITION p2016_04_03 VALUES LESS THAN (1459728000) ENGINE = InnoDB, PARTITION p2016_04_04 VALUES LESS THAN (1459814400) ENGINE = InnoDB, PARTITION p2016_04_05 VALUES LESS THAN (1459900800) ENGINE = InnoDB, PARTITION p2016_04_06 VALUES LESS THAN (1459987200) ENGINE = InnoDB, PARTITION p2016_04_07 VALUES LESS THAN (1460073600) ENGINE = InnoDB, PARTITION p2016_04_08 VALUES LESS THAN (1460160000) ENGINE = InnoDB, PARTITION p2016_04_09 VALUES LESS THAN (1460246400) ENGINE = InnoDB, PARTITION p2016_04_10 VALUES LESS THAN (1460332800) ENGINE = InnoDB, PARTITION p2016_04_11 VALUES LESS THAN (1460419200) ENGINE = InnoDB)
show index from history_uint; +--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | history_uint | 1 | history_uint_1 | 1 | itemid | A | 1752511 | NULL | NULL | | BTREE | | | | history_uint | 1 | history_uint_1 | 2 | clock | A | 848740800 | NULL | NULL | | BTREE | | | +--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+