国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

MYSQL的ibtmp1文件太大問題

IT那活兒 / 1915人閱讀
MYSQL的ibtmp1文件太大問題

點擊上方“IT那活兒”,關(guān)注后了解更多內(nèi)容,不管IT什么活兒,干就完了?。。?/span>

認識ibtmp1

該參數(shù)是5.7的新特性。

針對臨時表及相關(guān)對象引入新的“non-redo” undo log,存放于臨時表空間。該類型的undo log非 redolog 因為臨時表不需崩潰恢復、也就無需redo logs,但卻需要 undo log用于回滾、MVCC等。

默認的臨時表空間文件為ibtmp1,位于數(shù)據(jù)目錄在每次服務(wù)器啟動時被重新創(chuàng)建,可通過innodb_temp_data_file_path指定臨時表空間。

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


常見的使用tmp臨時表空間的場景

當EXPLAIN 查看執(zhí)行計劃結(jié)果的 Extra 列中,如果包含 Using Temporary 就表示會用到臨時表,例如如下幾種常見的情況通常就會用到:
1、UNION查詢(MySQL 5.7起,執(zhí)行UNION ALL不再產(chǎn)生臨時表,除非需要額外排序);
2、用到TEMPTABLE算法或者是UNION查詢中的視圖;
3、ORDER BY和GROUP BY的子句不一樣時;
4、表連接中,ORDER BY的列不是驅(qū)動表中的;
5、DISTINCT查詢并且加上ORDER BY時;
6、SQL中用到SQL_SMALL_RESULT修飾符的查詢;
7、FROM中的子查詢(派生表);
8、子查詢或者semi-join時創(chuàng)建的表;

9、評估多表UPDATE語句。


實例分析

1、問題現(xiàn)象

Mysql服務(wù)磁盤空間告警,經(jīng)過排查發(fā)現(xiàn)ibtmp1文件非常大,已經(jīng)超過TB級別,其他能清理的數(shù)據(jù)已經(jīng)做了清理。
ll -h ibtmp1
-rw-r----- 1 mysql mysql 1.2T Aug 15 16:17 ibtmp1

2、問題分析

通過了解了ibtmp1是非壓縮的innodb臨時表的獨立表空間,而且檢查配置發(fā)現(xiàn)為ibtmp1:12M:autoextend,也就是支持無限擴大的,才導致了ibtmp1文件增加打了非常大的一個數(shù)值。

3、解決方案

重啟數(shù)據(jù)庫,釋放臨時表空間,同時為了避免臨時表空間再次膨脹,可以將其設(shè)置一個最大的值。
--操作流程:
1)關(guān)閉數(shù)據(jù)庫實例。
關(guān)閉后ibtmp1文件會自動清理。
2)修改my.cnf配置文件,限制tmp表空間的大小。
為了避免ibtmp1文件無止境的暴漲導致再次出現(xiàn)此情況,可以修改參數(shù),限制其文件最大尺寸。
如果文件大小達到上限時,需要生成臨時表的SQL無法被執(zhí)行(一般這種SQL效率也比較低,可借此機會進行優(yōu)化)。
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:50G # 12M代表文件初始大小,50G代表最大size
3)啟動mysql服務(wù),查一下是否生效。
show  variables like innodb_temp_data_file_path;
+----------------------------+-------------------------------+
| Variable_name | Value |
+----------------------------+-------------------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend:max:50G |
+----------------------------+-------------------------------+


臨時表使用的幾點建議

1、設(shè)置innodb_temp_data_file_path選項,設(shè)定文件最大上限(innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:200M),超過上限時,需要生成臨時表的SQL無法被執(zhí)行(一般這種SQL效率也比較低,可借此機會進行優(yōu)化)。
2、檢查INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO,找到最大的臨時表對應(yīng)的線程,kill之即可釋放,但ibtmp1文件則不能釋放(除非重啟)。
3、擇機重啟實例,釋放ibtmp1文件,和ibdata1不同,ibtmp1重啟時會被重新初始化而ibdata1則不可以。
4、定期檢查運行時長超過N秒(比如N=300)的SQL,考慮清理,避免垃圾SQL長時間運行影響業(yè)務(wù)。


本文作者:徐林

本文來源:IT那活兒(上海新炬王翦團隊)

文章版權(quán)歸作者所有,未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請注明本文地址:http://specialneedsforspecialkids.com/yun/129633.html

相關(guān)文章

  • Docker Compose 1.18.0 之服務(wù)編排詳解

    摘要:新建一個你能記住的目錄,這個目錄是應(yīng)用鏡像的上下文,該目錄用于存放構(gòu)建該鏡像的資源在這個目錄里面將會新建一個文件進入目錄創(chuàng)建一個文件,將啟動您的博客和一個單獨的實例并掛載數(shù)據(jù)持久化到宿主機內(nèi)容如下指定服務(wù)的鏡像名稱或鏡像。 一個使用Docker容器的應(yīng)用,通常由多個容器組成。使用Docker Compose,不再需要使用shell腳本來啟動容器。在配置文件中,所有的容器通過servic...

    Seay 評論0 收藏0

發(fā)表評論

0條評論

IT那活兒

|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<