Zabbix碰见mysql5.7怪现象
日期:2017-06-17 01:45
Hi,I,m Zaki,此刻已经凌晨1点45了,虽然明天已经约好了要早上8点起床去重邮玩耍,但是现在完全没有睡意,一个困扰我长时间的问题终于解决了,开心~
由于现网换数据库的版本,将之前的mariadb5.5
升级到了percona5.7
,虽然percona
这个数据用的人不多,但是我也斗胆的尝试了一下,将现网都业务都换成了percona5.7
。数据库升级迁移工作异常顺利,但是在迁移线上zabbix
业务到新数据库的时候,发现大量SQL
被卡住,无法执行,类似这样的SQL:select clock,ns,value from history_uint where itemid=2831178 and clock>1484822156 and clock<=1484822336 order by clock desc limit 1;
看了一下应该是去查询出一个时间段最后一个值,为什么这样一个SQL迁移到新版本会执行几十秒,而在老版本数据库里面只需要0.01s呢?性能差距接近千倍,升级数据库版本不会造成这么大的问题吧。
我们一起来一探究竟~
问题分析
先使用explain
在percona5.7
上对该sql
进行分析
mysql> explain select clock,ns,value from history_uint where itemid=2831178 and clock>1484822156 and clock<=1484822336 order by clock desc limit 1;
+----+-------------+--------------+---------------+-------+----------------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+---------------+-------+----------------------+----------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | history_uint | p201701190000 | range | history_uint_1,clock | history_uint_1 | 12 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+--------------+---------------+-------+----------------------+----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
通过索引查询到一行上,应该是一个很快的SQL
才对,为什么就这么慢了呢?
我们在mariadb5.5
上对该sql再次进行分析
mysql> explain select clock,ns,value from history_uint where itemid=2831178 and clock>1484822156 and clock<=1484822336 order by clock desc limit 1;
+------+-------------+--------------+-------+----------------------+----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+-------+----------------------+----------------+---------+------+------+-------------+
| 1 | SIMPLE | history_uint | range | history_uint_1,clock | history_uint_1 | 12 | NULL | 1 | Using where |
+------+-------------+--------------+-------+----------------------+----------------+---------+------+------+-------------+
1 row in set (0.00 sec)
也是通过了索引直接查询到该数据
仔细对比之后,发现一个问题:两个版本的Extra内容不一样,mariadb5.5
是 Using where
,而percona5.7
是Using index condition
,难道是这个地方出现了问题?
通过查阅资料得知,ICP
开启时的执行计划 含有 Using index condition
标示 ,表示优化器使用了ICP
对数据访问进行优化。
那么,ICP是什么?
Index Condition Pushdown (ICP)
是MySQL 5.6
版本中的新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式。
1)当关闭ICP
时,index
仅仅是data access
的一种访问方式,存储引擎通过索引回表获取的数据会传递到MySQL Server
层进行where
条件过滤。
2)当打开ICP
时,如果部分where
条件能使用索引中的字段,MySQL Server
会把这部分下推到引擎层,可以利用index
过滤的where
条件在存储引擎层进行数据过滤,而非将所有通过index access
的结果传递到MySQL server
层进行where
过滤.
优化效果:ICP
能减少引擎层访问基表的次数和MySQL Server
访问存储引擎的次数,减少io次数,提高查询语句性能。
问题原因
原来我们查询的表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`),
KEY `clock` (`clock`),
KEY `idx_all` (`itemid`,`clock`,`ns`,`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`clock`)
(PARTITION p201701120000 VALUES LESS THAN (1484236800) ENGINE = InnoDB,
PARTITION p201701130000 VALUES LESS THAN (1484323200) ENGINE = InnoDB,
PARTITION p201701140000 VALUES LESS THAN (1484409600) ENGINE = InnoDB,
PARTITION p201701150000 VALUES LESS THAN (1484496000) ENGINE = InnoDB,
PARTITION p201701160000 VALUES LESS THAN (1484582400) ENGINE = InnoDB,
PARTITION p201701170000 VALUES LESS THAN (1484668800) ENGINE = InnoDB,
PARTITION p201701180000 VALUES LESS THAN (1484755200) ENGINE = InnoDB,
PARTITION p201701190000 VALUES LESS THAN (1484841600) ENGINE = InnoDB,
PARTITION p201701200000 VALUES LESS THAN (1484928000) ENGINE = InnoDB,
PARTITION p201701210000 VALUES LESS THAN (1485014400) ENGINE = InnoDB,
PARTITION p201701220000 VALUES LESS THAN (1485100800) ENGINE = InnoDB,
PARTITION p201701230000 VALUES LESS THAN (1485187200) ENGINE = InnoDB,
PARTITION p201701240000 VALUES LESS THAN (1485273600) ENGINE = InnoDB,
PARTITION p201701250000 VALUES LESS THAN (1485360000) ENGINE = InnoDB) */;
在MySQL5.7中,ICP有一个bugs,ICP不能正常的在有索引的分区表下进行工作。
Partitioning: Index condition pushdown did not work with partitioned tables. (Bug #17306882, Bug #70001)
ICP的关闭与开启
my.cnf
配置文件里面添加:
optimizer_switch=index_condition_pushdown=off
当然这个参数也支持在线修改:
SET [GLOBAL] optimizer_switch='index_condition_pushdown=off'
.
处理结果
关闭了ICP
优化器之后,发现查询瞬间到了毫秒级,我们再次使用explain
进行分析看看
mysql> explain select clock,ns,value from history_uint where itemid=3279183 and clock>1484822990 and clock<=1484823170 order by clock desc limit 1;
+----+-------------+--------------+---------------+-------+----------------------+----------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+---------------+-------+----------------------+----------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | history_uint | p201701190000 | range | history_uint_1,clock | history_uint_1 | 12 | NULL | 1 | 100.00 | Using where |
+----+-------------+--------------+---------------+-------+----------------------+----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
使用的Extra变成了Using where,这样问题就解决了,终于可以升级zabbix的数据库到percona5.7。
参考资料
MySQL · 特性分析 · Index Condition Pushdown (ICP)