Zabbix碰见mysql5.7怪现象

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。

参考资料

MySQL5.7 ICP 官网详解

MySQL · 特性分析 · Index Condition Pushdown (ICP)

Zabbix连接Mysql5.7进行查询使用到ICP导致查询缓慢bug

MySQL5.7 在分区表上进行查询排序导致性能问题解决方法