Details
-
Type:
Incident report
-
Status: Closed
-
Priority:
Blocker
-
Resolution: Fixed
-
Affects Version/s: 2.4.7
-
Fix Version/s: None
-
Component/s: Server (S)
-
Labels:
-
Environment:Zabbix 2.4
MySQL 5.6/5.7
Description
It takes a lot of time (hours!) to get values with select clock,ns,value from history_uint where itemid=<itemid> and clock<=<timestamp> and clock><timestamp> order by clock desc limit 1 query from a big partitioned DB (e.g. ~40-50GB per each partition). These queries lead to 100% disk IO on the DB server.
A possible solution is to replace the slow SQL query:
select clock,ns,value from history_uint where itemid=123 and clock<=1460364250 and clock>1459759450 order by clock desc limit 1
with these two:
select max(clock) from history_uint where itemid=123 and clock<=1460364250 and clock>1459759450; select clock,ns,value from history_uint where itemid=123 and clock=1460364197 order by ns desc limit 1;