作者：宗楊
愛可生產品交付團隊成員，主要負責公司運維平臺和數據庫運維故障診斷。喜愛數據庫、容器等技術，愛好歷史、追劇。
本文來源：原創投稿
*愛可生開源社區出品，原創內容未經授權不得隨意使用，轉載請聯繫小編並註明來源。
一、事件背景
我們的合作客戶，駐場人員報告說一個 RDS 實例出現磁盤不足的告警，需要排查。
告警信息：
告警內容：
數據庫 data 磁盤不足，磁盤佔用 80% 以上
數據庫 binlog 磁盤不足，磁盤佔用 80% 以上
二、排查過程
登陸告警的服務器，查看磁盤空間，並尋找大容量文件後，發現端口號為 4675 的實例臨時表空間 ibtmp1 的大小有 955G，導致磁盤被使用了 86%；
猜測和庫裡執行長 SQL 有關係，產生了很多臨時數據，並寫入到臨時表空間。
看到有這樣一條 SQL，繼續分析它的執行計劃；
很明顯看到圖中標記的這一點為使用了臨時計算，說明臨時表空間的快速增長和它有關係。這條 SQL 進行了三表關聯，每個表都有幾十萬行數據，三表關聯並沒有在 where 條件中設置關聯字段，形成了笛卡爾積，所以會產生大量臨時數據；而且都是全表掃描，加載的臨時數據過多;還涉及到排序產生了臨時數據；這幾方面導致 ibtmp1 空間快速爆滿。
三、解決辦法
和項目組溝通後，殺掉這個會話解決問題；
但是這個 SQL 停下來了，臨時表空間中的臨時數據沒有釋放；
最後通過重啟 mysql 數據庫，釋放了臨時表空間中的臨時數據，這個只能通過重啟釋放。
四、分析原理
通過查看官方文檔，官方是這麼解釋的：
翻譯：
根據官網文檔的解釋，在正常關閉或初始化中止時，將刪除臨時表空間，並在每次啟動服務器時重新創建。重啟能夠釋放空間的原因在於正常關閉數據庫，臨時表空間就被刪除了，重新啟動後重新創建，也就是重啟引發了臨時表空間的重建，重新初始化，所以，重建後的大小為 12M。
從錯誤日誌裡可以驗證上面的觀點：
五、官網對於 ibtmp1 大小的說明
六、如何避免
1. 對臨時表空間的大小進行限制，允許自動增長，但最大容量有上限,本例中由於 innodb_temp_data_file_path 設置的自動增長，但未設上限，所以導致 ibtmp1
有 955G。
正確方法配置參數 innodb_temp_data_file_path：
[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M
參考官方文檔：
設置了上限的大小，當數據文件達到最大大小時，查詢將失敗，並顯示一條錯誤消息，表明表已滿，查詢不能往下執行，避免 ibtmp1 過大。
2. 在發送例如本例中的多表關聯 SQL 時應確保有關聯字段而且有索引，避免笛卡爾積式的全表掃描，對存在 group by、order by、多表關聯的 SQL 要評估臨時數據量，對 SQL 進行審核，沒有審核不允許上線執行。
3. 在執行前通過 explain 查看執行計劃，對 Using temporary 需要格外關注。
七、其他補充
1> 通過字典表查看執行的 SQL 產生臨時表、使用臨時表空間的情況：
查詢字典表：sys.x$statements_with_temp_tables
select * from sys.x$statements_with_temp_tables where query like 'select%' and db='test' order by tmp_tables_to_disk_pct,disk_tmp_tables desc\G;
查詢字典表：sys.statements_with_temp_tables
select * from sys.statements_with_temp_tables where query like 'select%' and db='test' order by tmp_tables_to_disk_pct,disk_tmp_tables desc\G;
這兩個表查詢的結果是一樣的，各列含義如下：
query：規範化的語句字符串。
db：語句的默認數據庫， NULL 如果沒有。
exec_count：語句已執行的總次數。
total_latency：定時出現的語句的總等待時間。
memory_tmp_tables：由該語句的出現創建的內部內存臨時表的總數。
disk_tmp_tables：由該語句的出現創建的內部磁盤臨時表的總數。
avg_tmp_tables_per_query：每次出現該語句創建的內部臨時表的平均數量。
tmp_tables_to_disk_pct：內部內存臨時表已轉換為磁盤表的百分比。
first_seen：第一次看到該聲明的時間。
last_seen：最近一次發表該聲明的時間。
digest：語句摘要。
參考鏈接：https://dev.mysql.com/doc/refman/5.7/en/sys-statements-with-temp-tables.html
通過字典表 tmp_tables_to_disk_pct 這一列結果可知，內存臨時表已轉換為磁盤表的比例是 100%，說明通過復現這個查詢，它的臨時計算結果已經都放到磁盤上了，進一步證明這個查詢和臨時表空間容量的快速增長有關係。
2> 對於 mysql5.7 中 kill 掉運行長 SQL 的會話，ibtmp1 容量卻沒有收縮問題的調研；
來源鏈接：http://mysql.taobao.org/monthly/2019/04/01/
從文章中的解釋看，會話被殺掉後，臨時表是釋放的，只是在 ibtmp1 中打了刪除標記，空間並沒有還給操作系統，只有重啟才可以釋放空間。
3> 下面，進一步用 mysql8.0 同樣跑一下這個查詢，看是否有什麼不同;
mysql 版本：8.0.18
當這個 sql 將磁盤跑滿之後，發現與 5.7 不同的是這個 SQL 產生的臨時數據保存到了 tmpdir，mysql5.7 是保存在 ibtmp1 中，而且由於磁盤滿，SQL 執行失敗，很快磁盤空間就釋放了；
問題：如何使用到 8.0 版本的臨時表空間？
通過查看 8.0 的官方文檔得知，8.0 的臨時表空間分為會話臨時表空間和全局臨時表空間，會話臨時表空間存儲用戶創建的臨時表和當 InnoDB 配置為磁盤內部臨時表的存儲引擎時由優化器創建的內部臨時表，當會話斷開連接時，其臨時表空間將被截斷並釋放回池中；也就是說，在 8.0 中有一個專門的會話臨時表空間，當會話被殺掉後，可以回收磁盤空間；而原來的 ibtmp1 是現在的全局臨時表空間，存放的是對用戶創建的臨時表進行更改的回滾段，在 5.7 中 ibtmp1 存放的是用戶創建的臨時表和磁盤內部臨時表；
也就是在 8.0 和 5.7 中 ibtmp1 的用途發生了變化，5.7 版本臨時表的數據存放在 ibtmp1 中，在 8.0 版本中臨時表的數據存放在會話臨時表空間，如果臨時表發生更改，更改的 undo 數據存放在 ibtmp1 中；
實驗驗證：將之前的查詢結果保存成臨時表，對應會話是 45 號，通過查看對應字典表，可知 45 號會話使用了 temp_8.ibt 這個表空間，通過把查詢保存成臨時表，可以用到會話臨時表空間，如下圖：
下一步殺掉 45 號會話，發現 temp_8.ibt 空間釋放了，變為了初始大小，狀態為非活動的，證明在 mysql8.0 中可以通過殺掉會話來釋放臨時表空間。
總結：在 mysql5.7 時，殺掉會話，臨時表會釋放，但是僅僅是在 ibtmp 文件裡標記一下，空間是不會釋放回操作系統的。如果要釋放空間，需要重啟數據庫；在 mysql8.0 中可以通過殺掉會話來釋放臨時表空間。
八、參考文檔
https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-temporary-tablespace.html
http://mysql.taobao.org/monthly/2019/04/01/
