故障分析 | 從 data_free 異常說起

愛可生雲資料庫 發表於 2022-06-29

作者:楊奇龍

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

本文來源:原創投稿

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


一 前言

某個客戶反饋查詢資料庫發現 information_schema.tablesdata_free 值突發異常,達到 13G 左右。如圖:

故障分析 | 從 data_free 異常說起

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

二 排查

2.1 分析

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

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

故障分析 | 從 data_free 異常說起

看結果猜測和主庫上的某些請求動作有關,空洞是 MySQL 因為 sql 寫入而請求分配的空間沒有自動回收的結果。基於前線給的資訊,沒有其他思路,再看前線發的截圖:

故障分析 | 從 data_free 異常說起

意外從 截圖的 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 檔案大小:

[[email protected] 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

[[email protected] 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.tablesdata_free 的值:

故障分析 | 從 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 就表示會用到臨時表,例如如下幾種常見的情況通常就會用到:

  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_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 檔案空間佔用的問題

  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/ref...

https://dev.mysql.com/doc/ref...