MYSQL的ibtmp1文件太大怎麼處理

2020-11-25 站長之家

1.  啥情況呀

測試環境機器磁碟空間不足的告警打破了下午的沉寂，一群人開始忙活著刪數據。但是，不久前剛清理了一波數據，測試環境在沒做壓測的情況下不至於短短一個月不到就漲了200G數據，於是，我悄悄的進入數據目錄下，發現一個不尋常的點，ibtmp1 文件有192G

 ll -hibtmp1 -rw-r----- 1 mysql mysql 192G Aug 12 16：20 ibtmp1

2.   怎麼處理

2.1  簡單說明

ibtmp1 是非壓縮的innodb臨時表的獨立表空間，通過innodb_temp_data_file_path參數指定文件的路徑，文件名和大小，默認配置為ibtmp1：12M：autoextend，也就是說在支持大文件的系統這個文件大小是可以無限增長的。

2.2  解決辦法

a)  找個空閒時間關閉數據

#設置innodb_fast_shutdown參數SET GLOBAL innodb_fast_shutdown = 0;# 此步驟可以省略#關閉資料庫實例shutdown;   #  因本實例為MySQL5.7  可以直接在SQL命令行中shutdown關閉

關閉後ibtmp1 文件會自動清理

b)  修改my.cnf配置文件

為了避免ibtmp1 文件無止境的暴漲導致再次出現此情況，可以修改參數，限制其文件最大尺寸。

如果文件大小達到上限時，需要生成臨時表的SQL無法被執行(一般這種SQL效率也比較低，可藉此機會進行優化)

 innodb_temp_data_file_path = ibtmp1：12M：autoextend：max：5G  # 12M代表文件初始大小，5G代表最大size

c) 啟動mysql服務

啟動資料庫後可以查一下是否生效

mysql> show  variables like 'innodb_temp_data_file_path';+----------------------------+-------------------------------+| Variable_name              | Value                         |+----------------------------+-------------------------------+| innodb_temp_data_file_path | ibtmp1：12M：autoextend：max：5G |+----------------------------+-------------------------------+1 row in set (0.01 sec)

 3.  什麼情況下會用到臨時表

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

a)  GROUP BY 無索引欄位或GROUP  BY+ ORDER  BY 的子句欄位不一樣時

/**先看一下表結構 */mysql> show  create tabletest_tmp1\G*************************** 1. row ***************************       Table： test_tmp1Create Table： CREATE TABLE `test_tmp1` (`id` int(11) NOT NULL AUTO_INCREMENT，`name` varchar(50) DEFAULT NULL，`col2` varchar(25) DEFAULT NULL，PRIMARY KEY (`id`)，KEY `name` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf81 row in set (0.00 sec)/**groupby無索引欄位*/mysql> explain select * from test_tmp1 group bycol2 ;+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+|  1 | SIMPLE      | test_tmp1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | Using temporary; Using filesort |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+/**group by 與order by欄位不一致時，及時group by和order by欄位有索引也會使用 */mysql> explain select name from test_tmp1 group by  name order by id desc;+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                     |+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+|  1 | SIMPLE      | test_tmp1 | NULL       | range | name          | name | 153     | NULL |    3 |   100.00 | Using index for group-by; Using temporary; Using filesort |+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+1 row in set， 1 warning (0.02 sec)

b)  order by  與distinct 共用，其中distinct與order by裡的欄位不一致(主鍵欄位除外)

/**例子中有無索引時會存在，如果 2 個欄位都有索引會如何*/mysql> alter table  test_tmp1 add key col2(col2);Query OK， 0 rows affected (1.07 sec)Records： 0  Duplicates： 0  Warnings： 0/** 結果如下，其實該寫法與group by +order by 一樣*/mysql> explain select distinct col2  from test_tmp1 order  byname;+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------+| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------+|  1 | SIMPLE      | test_tmp1 | NULL       | index | col2          | col2 | 78      | NULL |    8 |   100.00 | Using temporary; Using filesort |+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------+1 row in set， 1 warning (0.00 sec)

 c)  UNION查詢(MySQL5. 7 後union all已不使用臨時表)

/**先測一下union all的情況*/mysql> explain select name from test_tmp1 union all  select name from test_tmp1 where id <10;+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+|  1 | PRIMARY     | test_tmp1 | NULL       | index | NULL          | name    | 153     | NULL |    8 |   100.00 | Using index ||  2 | UNION       | test_tmp1 | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    8 |   100.00 | Using where |+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+2 rows in set， 1 warning (0.01 sec)/**再看一下union 作為對比，發現出現了使用臨時表的情況*/mysql> explain select name from test_tmp1 union   select name from test_tmp1 where id <10;+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+|  1 | PRIMARY      | test_tmp1  | NULL       | index | NULL          | name    | 153     | NULL |    8 |   100.00 | Using index     ||  2 | UNION        | test_tmp1  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    8 |   100.00 | Using where     || NULL | UNION RESULT | <union1，2> | NULL       | ALL   | NULL        | NULL    | NULL    | NULL | NULL |     NULL | Using temporary |+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+3 rows in set， 1 warning (0.00 sec)

d)  insert into select ...from ...

/**簡單看一下本表的數據重複插入的情況 */mysql> explain insert into test_tmp1(name，col2)  select name，col2 from test_tmp1;+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+|  1 | INSERT      | test_tmp1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | NULL            ||  1 | SIMPLE      | test_tmp1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | Using temporary |+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+2 rows in set (0.00 sec)

小結：  上面列舉的是最常見的使用臨時表的情況，其中基本都是引起慢查詢的因素，因此，如果遇到臨時表空間文件暴漲是需要查看一下是否有大量的慢查詢。

4.  和臨時表空間相關的參數有哪些

各參數之間相互影響，其中直接影響臨時表空間的參數如要有如下幾個

innodb_temp_data_file_path tmp_table_sizemax_heap_table_sizedefault_tmp_storage_engineinternal_tmp_disk_storage_engine

5.  下面來模擬一個ibtmp1 文件快速膨脹的例子

5.1  調整參數值

上面列出了主要的參數，那麼先調整一下參數，以便於模擬

tmp_table_size = 16Minnodb_temp_data_file_path = ibtmp1：12M：autoextend：max：5G

調整後重啟資料庫

5.2   造一批數據

/**造一張表或者從其他表複製一批數據，為了方便模擬，可以不創建主鍵及索引*/mysql> create table test_tmp3 select  * from db1.tbname;Query OK， 15948372 rows affected (2 min 27.24 sec)Records： 15948372  Duplicates： 0  Warnings： 0

此時查看一下ibtmp1 文件的大小

ll -h ibtmp1 -rw-r----- 1 mysql mysql 12M Aug 15 16：06 ibtmp1  /**此時是默認的初始大小*/

5.2  使用insert into ... select * from ...的方式插入

/**此方式將會使用臨時表空間，且 tmp_table_size參數已調小為16M，本表當前有2G多，所以會使用臨時表空間*/mysql> insert into  test_tmp3 select  * from test_tmp3 ;Query OK， 15948372 rows affected (2 min 7.40 sec)Records： 15948372  Duplicates： 0  Warnings： 0

此時 查看一下ibtmp1 文件的大小

 ll -h ibtmp1 -rw-r----- 1 mysql mysql 2.8G Aug 15 16：17 ibtmp1  /**此時已使用了2.8G*/

此時該表的size如下

 ll -h bak_db/test_tmp3*                          /** 結果中已有5.8G*/-rw-r----- 1 mysql mysql 8.9K Aug 15 16：04 bak_db/test_tmp3.frm-rw-r----- 1 mysql mysql 5.8G Aug 15 16：16 bak_db/test_tmp3.ibd

5.3  繼續測試，看看會發生什麼

因為ibtmp1 當前設置的最大值為5G，繼續複製一個5.8G的數據，會不會異常，如果異常有什麼表現?

/** 繼續插入時 因臨時表空間大小有限制，超過5G後將異常，信息如下*/mysql> insert into  test_tmp3 select  * from test_tmp3;ERROR 1114 (HY000)： The table '/app/data/mysql3306/tmp/#sql_32469_0' is full

此時 查看一下ibtmp1 文件的大小

 ll -h ibtmp1 -rw-r----- 1 mysql mysql 5.0G Aug 15 16：17 ibtmp1/**此時已使用了5.0G，已達到上限*/

資料庫日誌裡也會記錄本次異常

2019-08-15T08：23：47.016495Z 3 [ERROR] /usr/local/mysql5.7/bin/mysqld： The table '/app/data/mysql3306/tmp/#sql_32469_0' is full

以上測試實例因不同的版本可能會有差異，建議大家親自測試一下。

本文轉載自微信公眾號【資料庫乾貨鋪】。

相關焦點

  • PDF文件太大怎麼壓縮?超實用方法推薦!
    PDF文件太大怎麼壓縮？我們在系統上傳PDF文件的時候，由於系統限制，PDF大小受到了限制，我們需要對PDF進行壓縮小一點進行上傳。今天我們來說一個小技巧，就是對PDF文件大小的壓縮。具體怎麼操作，一起來看看吧！
  • mysql怎麼連接資料庫
    今天介紹一下php搭配資料庫mysql怎麼使用。如果在自己電腦上運行，可以先打開運行環境，小編用的是WampServer，網上有很多集成包可以使用，比如wamp，phpstudy等等，這個有疑問的話小編可以單獨寫篇文章介紹php環境問題。
  • MySQL底層存儲結構
    67 Dec 6 14:24 db.opt 12 -rw-r 1 mysql mysql 8674 Dec 24 10:51 t_innodb.frm # 表結構定義文件 96 -rw-r 1 mysql mysql 98304 Dec 24 10:54 t_innodb.ibd # 表空間文件，裡面存放數據和索引 12 -rw-r 1 mysql mysql 8674 Dec
  • PDF文件如何壓縮?手機中的PDF文件太大怎麼壓縮?
    PDF文件如何壓縮？手機中的PDF文件太大怎麼壓縮？而手機中的PDF文件往往需要通過微信、郵箱等傳輸路徑，傳送給他人，但有一定的文件大小限制，當PDF文件超過一定的大小就無法進行傳輸，文件又比較緊急如何解決。介紹一款《迅捷PDF閱讀器》軟體給大家。
  • Centos7系統mysql登錄拒絕處理
    當centos7系統安裝mysql5.7，進入mysql的時候報錯：「Access denied for user 'root'@'localhost' (using password:yes)」則說明mysql用戶名和密碼錯誤，就需要無密碼進入，將root的密碼修改為指定的密碼
  • MySQL怎麼刪除#sql開頭的臨時表
    處理方法3.1   同時存在.frm 和.ibd名稱相同的文件如果 #sql-*.ibd 和 #sql-*.frm兩個文件都存在數據目錄裡的話，可以直接drop table。但注意刪除時候表名的變化。/* 直接刪除，表名前加#mysql50*/root@testdb 01：42：57> DROP TABLE `#mysql50##sql-ib87-856498050`;註： #mysql50#前綴是MySQL 5. 1 中引入的文件名安全編碼。另外，表名因不符合命名規範，想要執行該腳本需要將表名用反引號括起來。
  • pdf文件太大怎麼變小?pdf文件要怎麼壓縮?
    平常要去壓縮一個文件，我們都知道怎麼操作，s系統中自帶有壓縮工具，只需要用滑鼠右鍵點擊需要壓縮的對象，然後選擇壓縮就可以了。但是相信大家也發現了，一般的文件是可以這樣壓縮的，但是PDF文件卻不能這樣壓縮。那麼pdf文件太大怎麼變小？pdf文件要怎麼壓縮？
  • 誤刪除MySQL資料庫表的ibd文件怎麼辦
    很多年前，學習oracle10g的技術時，看過蓋國強老師寫的一篇文章，在linux系統中，oracle資料庫存活的時候，用系統命令rm刪除一個數據文件，這個時候找到刪除文件的fd文件句柄，就可以將刪除的文件找回，當時感覺特別的神奇，竟然還有這種騷操作。
  • 如何修復MySQL配置文件?
    當主機名不允許連接到MySQL伺服器時：通過#mysql授予用戶訪問權限 # mysql mysql -snip-mysql> CREATE USER 『user』@』hostname』 IDENTIFIED BY 『password』; Query OK, 0 rows affected (0.00 sec)mysql> GRANT ALL PRIVILEGES ON
  • pdf文件太大怎麼變小?有哪些PDF文件變小的簡單方法?
    pdf文件太大怎麼變小？我們在工作中的很多方面都需要用到PDF文件，這樣下來我們的電腦中就保存了大量的PDF文件。這些PDF文件不僅佔用大量電腦內存，而且如果我們要將一些大體積的PDF文件發送給別人，也會浪費很多的時間。面對這些問題，我們可以將PDF文件壓縮。PDF文件該如何壓縮呢？
  • PDF文件太大怎麼壓縮?分享PDF壓縮的方法!
    PDF文件太大怎麼壓縮？不管是辦公人員還是在校學生，都會經常接觸和使用PDF文件，但有些文件太大的話就很難保存和發送，這時可以先將PDF文件進行壓縮。那你知道用什麼方法可以壓縮PDF文件嗎？下面小編就給大家分享PDF壓縮的方法。
  • PDF文件太大怎麼縮小?分享PDF壓縮的簡單方法!
    PDF文件太大怎麼縮小？PDF格式文件越來越被人所熟知，也逐漸廣泛應用於各類辦公活動中。可是當使用的PDF文件體積太大時，傳輸起來會很麻煩，遇到這種情況我們可以把PDF文件進行壓縮。那麼PDF文件太大怎麼縮小呢？下面就來學習PDF文件的壓縮方法。
  • MySQL關於日期為零值的處理
    原來了解過和 sql_mode 參數設置有關，但還不是特別清楚，本篇文章將探究下MySQL怎麼處理日期值為零的問題。1.問題描述這裡我們說的日期為零值是指年、月、日為零，即&39;。2.嚴格模式下 不啟用NO_ZERO_DATEmysql> set session sql_mode = &34;;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> truncate table t_zerodate;Query OK, 0 rows affected (0.08 sec)mysql> insert into
  • PDF文件太大怎麼壓縮?一定要學會這個方法!
    PDF文件太大怎麼壓縮？不管是辦公人員還是在校學生，都會經常接觸和使用PDF文件，有的文件太大了就會佔用很多內存，而最好的方法就是把比較大的文件進行壓縮，那PDF文件怎麼壓縮呢？下面小編就飛大家介紹PDF文件壓縮的簡便的方法。
  • MySQL怎麼連接 全部連接方式匯總
    免密登錄的方式2.1  修改my.cnf的方式可以在my.cnf配置文件的[client]標籤下增加用戶信息處理。但是，該方式默認使用的/etc/my.cnf配置文件下的信息，因此需要調整該目錄下的對應標籤下的信息vim  /etc/my.cnf/**添加如下信息 */[client]user="root"password="123456"此時登錄，無需輸入密碼
  • PDF文件太大怎麼變小?如何調整PDF文件壓縮的大小?
    PDF文件太大怎麼變小？一份優質的PDF文件中需要有完整的文字、精美的配圖、準確的數據表格以及一些特殊內容。不過這樣的PDF文件體積是比較大的，在傳輸之前需要將文件壓縮變小。可是當PDF文件體積太大，我們怎麼將它進行壓縮呢？以下有幾個PDF壓縮方法供大家參考。
  • mysql怎麼處理大表在不停機的情況下增加欄位
    MySQL中給一張千萬甚至更大量級的表添加欄位一直是比較頭疼的問題，遇到此情況通常該如果處理?本文通過常見的三種場景進行案例說明。/bin/bash# gjcfor i in  {1..1000000000}# 訪問次數1000000000，按需調整即可do    id=$RANDOM#生成隨機數mysql -uroot -p'123456' --socket=/data/mysql3306/tmp/mysql.sock  -e "selecta.
  • mysql/mariadb - 服務到底加載哪個配置文件
    、程序默認的配置文件路徑MySQL/Mariadb有默認加載的幾個路徑的配置文件，可以從幫助裡面查看到：mysqld --verbose --help |grep -A 1 'Default options'2018-04-18 11:26:16 139865713512384
  • 3分鐘短文|MySQL備份和遷移sql文件,這個指令基礎又關鍵
    在 MySQL中我們使用下面的方式：mysql -u username -p database_name < file.sql在伺服器命令行上，運行 mysql 客戶端，並使用 username + password 進行身份驗證，然後指定 database_name，使用 <，指明將sql文件導入該資料庫內。
  • Linux中MySQL配置文件my.cnf參數優化
    [mysqld]user = mysql --- 表示MySQL的管理用戶port = 3306 --- 埠socket = /tmp/mysql.sock -- 啟動的sock文件log-bin = /data/mysql-binbasedir = /usr/local/mysqldatadir = /data/pid-file = /data/mysql.piduser =