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版本的深入研究