故障分析 | 從 data_free 異常說起

作者:楊奇龍

網名“北在南方”,資深 DBA,主要負責數據庫架構設計和運維平臺開發工作,擅長數據庫性能調優、故障診斷。

本文來源:原創投稿

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

一 前言

某個客戶反饋查詢數據庫發現 information_schema.tables 的 data_free 值突發異常,達到 13G 左右。如圖:

需要排查什么原因導致的,本文梳理排查的過程和和解決問題的方法。

二 排查

2.1 分析

首先 data_free 的含義是 表空間 ibd 文件經過寫入和刪除之后,留下的沒有回收的碎片空間大小。

讓現場的同學同時檢查主備庫,對比有沒有文件大小和配置上的差異。 發現主庫的data_free 值是 13G 左右, 備庫正常。

看結果猜測和主庫上的某些請求動作有關,空洞是 MySQL 因為 sql 寫入而請求分配的空間沒有自動回收的結果?；谇熬€給的信息,沒有其他思路,再看前線發的截圖:

意外從 截圖的 ibtmp1 文件大小找到一些線索,截圖顯示 ibtmp1 文件大小也是 13G ,備庫則是初始值大小。

忽略紅色的箭頭,查看 ibtmp1 文件大小為 13G ,似乎有些頭緒,data_free 是否和 ibtmp1 有關。

2.2 驗證猜想

使用 sysbench 創建測試表 sbtest1 ,構造2w條記錄,然后創建 sbtest2 ,將 sbtest1 的數據 導入到 sbtest2 。為何這么操作,后面會說明。

mysql > show  variables like 'innodb_temp_data_file_path';+----------------------------+-----------------------+| Variable_name              | Value                 |+----------------------------+-----------------------+| innodb_temp_data_file_path | ibtmp1:12M:autoextend |+----------------------------+-----------------------+1 row in set (0.00 sec)

查看物理ibtmp1 文件大小:

[root@tidb00 data]# du -sm ibtmp112ibtmp1

溝通測試用例,讓系統自動生成臨時表

mysql  > create table sbtest2 like sbtest1;Query OK, 0 rows affected (0.01 sec)mysql > insert into sbtest2(k,c,pad) select k,c ,pad from sbtest1;Query OK, 200000 rows affected (1.18 sec)Records: 200000  Duplicates: 0  Warnings: 0mysql > insert into sbtest2(k,c,pad) select k,c ,pad from sbtest1;Query OK, 200000 rows affected (1.06 sec)mysql > insert into sbtest2(k,c,pad) select k,c ,pad from sbtest2;Query OK, 400000 rows affected (2.49 sec)Records: 400000  Duplicates: 0  Warnings: 0mysql > insert into sbtest2(k,c,pad) select k,c ,pad from sbtest2;Query OK, 800000 rows affected (6.18 sec)Records: 800000  Duplicates: 0  Warnings: 0

再次檢查 ibtmp1 文件大小 204MB

[root@tidb00 data]# du -sm ibtmp1204ibtmp1mysql > select FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE    ->        AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES    ->        WHERE TABLESPACE_NAME = 'innodb_temporary'G*************************** 1. row ***************************      FILE_NAME: ./ibtmp1TABLESPACE_NAME: innodb_temporary         ENGINE: InnoDB   INITIAL_SIZE: 12582912 TotalSizeBytes: 213909504      DATA_FREE: 207618048  ## 和物理文件大小對應   MAXIMUM_SIZE: NULL1 row in set (0.00 sec)

查看 I_S.tables 的data_free 的值:

查看 insert select from table 在執行過程中的確使用了臨時表。

mysql > explain insert into sbtest2(k,c,pad) select k,c ,pad from sbtest2G*************************** 1. row ***************************..*************************** 2. row ***************************           id: 1  select_type: SIMPLE        table: sbtest2   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 1578168     filtered: 100.00        Extra: Using temporary  ## 2 rows in set (0.00 sec)Records: 200000  Duplicates: 0  Warnings: 0

至此,可以確定客戶的實例因為執行某些 SQL 過程中占用系統臨時表空間,使用完之后臨時表空間并未被回收導致開頭的問題。接下來我們詳細了解 MySQL 臨時表的相關知識。

三 臨時表空間

3.1 介紹

ibtmp1 是非壓縮的 innodb 臨時表的獨立表空間, 通過 innodb_temp_data_file_path參數指定文件的路徑,文件名和大小,默認配置為ibtmp1:12M:autoextend,如果沒有指定位置,臨時表空間會被創建到innodb_data_home_dir 指定的路徑。

需要注意的是: 按照默認值,這個文件大小是可以無限增長的。而且 5.7 版本并不會隨著 SQL 語句結束主動回收該臨時表空間,導致空間資源不足的安全風險。

3.2 什么情況下會用到臨時表

當 explain 查看執行計劃結果的 extra 列中,如果包含 Using Temporary 就表示會用到臨時表,例如如下幾種常見的情況通常就會用到:

1. insert into tab1 select ... from tab2 。
2. group by 無索引字段或 group by order by 的字段不一樣。
3. distinct 的值和 group by 的值不一樣,無法利用稀疏索引。

其他的歡迎補充。

3.3 臨時表相關的參數和元數據

5.7 版本:

innodb_temp_data_file_pathdefault_tmp_storage_engine internal_tmp_disk_storage_engine 

8.0 版本分為會話級和全局級臨時表空間

innodb_temp_tablespaces_dir #指定會話級創建臨時表到BASEDIR/data/#innodb_tempinnodb_temp_data_file_path # 全局變量internal_tmp_disk_storage_engine 

用戶自己創建的臨時表可以通過查詢 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO

mysql > CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;Query OK, 0 rows affected (0.00 sec)mysql  > select * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFOG*************************** 1. row ***************************            TABLE_ID: 54                NAME: #sqlfd5_b_0              N_COLS: 4               SPACE: 36PER_TABLE_TABLESPACE: FALSE       IS_COMPRESSED: FALSE1 row in set (0.00 sec)

MySQL 在執行 sql 過程中被優化器創建的表,則無法通過 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO 直接查看。比如本文的案例。

3.4 怎么解決 ibtmp1 文件空間占用的問題

1. 萬能的重啟大法, 找個合適的時間,切換數據庫,重啟老的主庫。
2. 通過配置 innodb_temp_data_file_path 控制ibtmp1 文件的最大值,避免表空間大小無限增加。

>  `innodb_temp_data_file_path` = ibtmp1:12M:autoextend:max:10G  

12M是文件的初始大小,10G是文件的最大值,超過最大值則系統會提示報錯

>ERROR 1114 (HY000): The table '/data/msb_5_7_31/tmp#sql_xxxxx_0' is full

參考文章

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