故障分析 | 從 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 ibtmp1
12    ibtmp1

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

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: 0
mysql > 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: 0
mysql > 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 ibtmp1
204    ibtmp1

mysql > 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: ./ibtmp1
TABLESPACE_NAME: innodb_temporary
         ENGINE: InnoDB
   INITIAL_SIZE: 12582912
 TotalSizeBytes: 213909504
      DATA_FREE: 207618048  ## 和物理檔案大小對應
   MAXIMUM_SIZE: NULL
1 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 sbtest2\G
*************************** 1. row ***************************
..
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest2
   partitions: NULL
         type: ALL
possible_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 就表示會用到臨時表,例如如下幾種常見的情況通常就會用到：

insert into tab1 select ... from tab2 。
group by 無索引欄位或 group by order by 的欄位不一樣。
distinct 的值和 group by 的值不一樣，無法利用稀疏索引。

其他的歡迎補充。

3.3 臨時表相關的引數和後設資料

5.7 版本:

innodb_temp_data_file_path
default_tmp_storage_engine 
internal_tmp_disk_storage_engine

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

innodb_temp_tablespaces_dir #指定會話級建立臨時表到BASEDIR/data/#innodb_temp
innodb_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_INFO\G
*************************** 1. row ***************************
            TABLE_ID: 54
                NAME: #sqlfd5_b_0
              N_COLS: 4
               SPACE: 36
PER_TABLE_TABLESPACE: FALSE
       IS_COMPRESSED: FALSE
1 row in set (0.00 sec)

MySQL 在執行 sql 過程中被優化器建立的表，則無法通過 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO 直接檢視。比如本文的案例。

3.4 怎麼解決 ibtmp1 檔案空間佔用的問題

萬能的重啟大法, 找個合適的時間，切換資料庫，重啟老的主庫。
通過配置 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/ref...

故障分析 | 從 data_free 異常說起

一 前言

二 排查