-
Incident report
-
Resolution: Fixed
-
Major
-
1.8.2
-
None
-
FreeBSD 7.2-RELEASE
Zabbix Server v1.8.2 (revision 11211) (29 March 2010)
mysql 5.0.86
slow query in mysql:
- Time: 100421 14:03:32
- User@Host: zabbix[zabbix] @ localhost []
- Query_time: 101 Lock_time: 0 Rows_sent: 83371 Rows_examined: 253038
select i.itemid,i.hostid,h.proxy_hostid,i.type,i.data_type,i.value_type,i.key_,i.snmp_community,i.snmp_oid,i.snmp_port,i.snmpv3_securityname,i.snmpv3_securitylevel,i.snmpv3_authpassphrase,i.snmpv3_privpassphrase,i.ipmi_sensor,i.delay,i.delay_flex,i.trapper_hosts,i.logtimefmt,i.params,i.status,i.authtype,i.username,i.password,i.publickey,i.privatekey from items i,hosts h where i.hostid=h.hostid and h.status in (0) and i.status in (0,3) and i.itemid between 000000000000000 and 099999999999999 order by i.itemid;
This query makes mysql use temporary table. The size of table reaches some times 500 Mb. The query really slows down all the system.
mysql> explain select i.itemid,i.hostid,h.proxy_hostid,i.type,i.data_type,i.value_type,i.key_,i.snmp_community,i.snmp_oid,i.snmp_port,i.snmpv3_securityname,i.snmpv3_securitylevel,i.snmpv3_authpassphrase,i.snmpv3_privpassphrase,i.ipmi_sensor,i.delay,i.delay_flex,i.trapper_hosts,i.logtimefmt,i.params,i.status,i.authtype,i.username,i.password,i.publickey,i.privatekey from items i,hosts h where i.hostid=h.hostid and h.status in (0) and i.status in (0,3) and i.itemid between 000000000000000 and 099999999999999 order by i.itemid;
---------------------------------------------------------------------------------------------------------------+
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------------------------------------+
1 | SIMPLE | h | ref | PRIMARY,hosts_2 | hosts_2 | 4 | const | 1173 | Using temporary; Using filesort |
1 | SIMPLE | i | ref | PRIMARY,items_1,items_3 | items_1 | 8 | zabbix.h.hostid | 14 | Using where |
---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)