Zabbix的events页面非常慢且消耗大量资源

Zabbix的events页面非常慢且消耗大量资源
问题描述
环境 CentOS5? Zabbix-1.4.2 MySQL? 5.1
Zabbix 正常运行了很久之后，突然有一天打开events.php页面，页面非常慢，刷新了几次，结果发现系统负载冲到10以上，差点死掉。

解决过程
经过调查，出现问题的时候，系统的监控表明数据库的slow log剧增，基本上判断是这个页面中有比较慢的查询所致。
进一步检查，slow log中有很多形如下面这样的SQL
# Time: 081203 6:35:12
# User@Host: zabbix[zabbix] @ [127.0.0.1]
# Query_time: 3 Lock_time: 0 Rows_sent: 2 Rows_examined: 1913303
SELECT MAX(clock) as clock, value FROM events WHERE objectid=12778 AND value<>2 AND clock < 1227976533 GROUP BY value ORDER BY clock DESC;# Time: 081203 6:40:03# User@Host: zabbix[zabbix] @ [127.0.0.1]# Query_time: 3 Lock_time: 0 Rows_sent: 2 Rows_examined: 1913312SELECT MAX(clock) as clock, value FROM events WHERE objectid=12778 AND value<>2 AND clock < 1227941133 GROUP BY value ORDER BY clock DESC;手动在mysql命令行下执行这样一句命令，耗时2s-4s不等，但是毋庸置疑的是，这个查询很慢。接下来考虑如何优化。执行explain分析SQL# 首先是表定义信息和建表语句（含索引定义）mysql> desc events;
+————–+———————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————–+———————+——+—–+———+——-+
| eventid | bigint(20) unsigned | NO | PRI | 0 | |
| source | int(11) | NO | | 0 | |
| object | int(11) | NO | MUL | 0 | |
| objectid | bigint(20) unsigned | NO | | 0 | |
| clock | int(11) | NO | MUL | 0 | |
| value | int(11) | NO | | 0 | |
| acknowledged | int(11) | NO | | 0 | |
+————–+———————+——+—–+———+——-+
7 rows in set (0.01 sec)

mysql> show create table eventsG
*************************** 1. row ***************************
Table: events
Create Table: CREATE TABLE `events` (
`eventid` bigint(20) unsigned NOT NULL default ‘0’,
`source` int(11) NOT NULL default ‘0’,
`object` int(11) NOT NULL default ‘0’,
`objectid` bigint(20) unsigned NOT NULL default ‘0’,
`clock` int(11) NOT NULL default ‘0’,
`value` int(11) NOT NULL default ‘0’,
`acknowledged` int(11) NOT NULL default ‘0’,
PRIMARY KEY (`eventid`),
KEY `events_1` (`object`,`objectid`,`clock`),
KEY `events_2` (`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

# 执行explain分析SQL
mysql> explain SELECT MAX(clock) as clock, value FROM events WHERE objectid=12778 AND value<>2 AND clock < 1228224333 GROUP BY value ORDER BY clock DESCG*************************** 1. row *************************** id: 1 select_type: SIMPLE table: events type: ALLpossible_keys: events_2 key: NULL key_len: NULL ref: NULL rows: 1914432 Extra: Using where; Using temporary; Using filesort # 这里就是关键了，性能就是因为这些，全表扫描，没有使用索引1 row in set (0.00 sec)经过检查发现，已有的两个索引并不能优化这个表的检索，下面是一个简单的说明mysql> explain SELECT clock, value FROM events WHERE clock < 1128224333G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: events type: rangepossible_keys: events_2 key: events_2 key_len: 4 ref: NULL rows: 1 Extra: Using where1 row in set (0.00 sec)mysql> explain SELECT clock, value FROM events WHERE clock < 1228224333G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: events type: ALLpossible_keys: events_2 key: NULL key_len: NULL ref: NULL rows: 1914898 Extra: Using where1 row in set (0.00 sec)mysql> explain SELECT clock, value FROM events force index(events_2) WHERE clock < 1228224333G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: events type: rangepossible_keys: events_2 key: events_2 key_len: 4 ref: NULL rows: 957455 Extra: Using where1 row in set (0.00 sec)# 上面三句分析表明，对于实际需要的clock范围，events_2的筛选度并不好，MySQL默认优化在30%以下的筛选才会选用这个索引# 所以最终结果是系统放弃使用已有索引最后的解决方法也不复杂，在这个查询语句上的objectid,value和clock上建立联合索引，一共不到200万条数据，加索引用了半分多钟mysql> alter table events add index (objectid,value,clock) ;
优化后，查询非常快，不到0.01秒，随之页面的问题也就解决了。
mysql> SELECT MAX(clock) as clock, value FROM events WHERE objectid=12778 AND value<>2 AND clock < 1228224333 GROUP BY value;+------------+-------+| clock | value |+------------+-------+| 1228209333 | 0 || 1228224303 | 1 |+------------+-------+2 rows in set (0.00 sec)mysql> explain SELECT MAX(clock) as clock, value FROM events WHERE objectid=12778 AND value<>2 AND clock < 1228224333 GROUP BY valueG*************************** 1. row *************************** id: 1 select_type: SIMPLE table: events type: rangepossible_keys: events_2,events_3 key: events_3 key_len: 12 ref: NULL rows: 3540 Extra: Using where; Using index; Using temporary; Using filesort1 row in set (0.00 sec)