5.7 ibtmp1问题诊断

环境：

OS：centos6.5      DB: mysql5.7.9（GA版本） 

搜索库实例的数据是从线上环境部分库中通过多源复制拉取而来的数据（线上数据与搜索数据做隔离），主要用来提供搜索的部分功能实现的查询（只有select）

问题：

收到zabbix报警，线上搜索库/data目录free space不足10%，cpu load达到460%，查看zabbix监控，BF刷新也是瞬间飙升

诊断：

1：/data目录前期规划是2T空间，在上一份的统计信息中显示，free space是28%

2：搜索库只拉取部分库的binlog，业务增长率load不到这样的高度

3：cpu负载瞬间飙升，iostat查看IO负载并不高，第一时间想到是慢查询，在processlist和trx表中发现了端倪，大量长时间的状态不对的查询语句

4：慢查询导致load值上升已确定。/data目录为何使用这么快？BF刷新频率为何上升？

解决办法：

1：通知搜索，停止相关查询任务，取出慢查询sql并做优化，语句大致为两个结果集做union，查询频率为1分钟一次，问题在第二个查询语句上，产生了临时表，且索引选择不佳（重建索引）

2：目录增长问题，去/data目录下du查看，增长的文件为ibtmp1，已结增长到了320G左右。查看官方文档ibtmp1，解释如下

MySQL 5.7.2 introduces a new type of undo log for both normal and compressed temporary
tables and related objects. The new type of undo log is not a redo log, as temporary tables are
not recovered during crash recovery and do not require redo logs. Temporary table undo logs are,
however, required for rollback, MVCC, and purging while the server is running. This special type
of non-redo undo log benefits performance by avoiding redo logging I/O for temporary tables and
related objects. The new undo log resides in the temporary tablespace. The default temporary
tablespace file, ibtmp1, is located in the data directory by default and is always recreated on
server startup. A user defined location for the temporary tablespace file can be specified by setting
innodb_temp_data_file_path

注意标红部分：5.7新引入了一个参数innodb_temp_data_file_path 来存放临时表和undo日志的表空间

这条sql频繁的查询导致了大量临时表的产生，BF刷新undo频繁，而ibtmp1就不断增大

想法：

1：sql审核力度。

2: 5.7版本的深入研究