作者：宗楊

愛可生產品交付團隊成員，主要負責公司運維平臺和數據庫運維故障診斷。喜愛數據庫、容器等技術，愛好歷史、追劇。

本文來源：原創投稿

*愛可生開源社區出品，原創內容未經授權不得隨意使用，轉載請聯系小編并注明來源。

一、事件背景

我們的合作客戶，駐場人員報告說一個 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 descG;

查詢字典表：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 descG;

這兩個表查詢的結果是一樣的，各列含義如下：

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/