tmp空間增長異常說明

 作者：sylar版權(quán)所有[文章允許轉(zhuǎn)載,但必須以鏈接方式注明源地址,否則追究法律責(zé)任.]

一、背景說明

     近日 zabbix總是如下報警信息：
  

為衢江等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計制作服務(wù)，及衢江網(wǎng)站建設(shè)行業(yè)解決方案。主營業(yè)務(wù)為網(wǎng)站設(shè)計、網(wǎng)站制作、衢江網(wǎng)站設(shè)計，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。我們深信只要達到每一位用戶的要求，就會得到認可，從而選擇與我們長期合作。這樣，我們也可以走得更遠！

二、問題分析

     根據(jù)zabbix報警，立即查詢空間，此時根目錄空間較大，不至于報警
 

      查看mysql的錯誤日志，發(fā)現(xiàn)該時間點有如下錯誤信息
 

       根據(jù)上圖錯誤提示，是tmp空間增長異常，并且是往里添加表（.myi格式結(jié)尾，且提及myisam，故做此判斷）

       查詢數(shù)據(jù)庫，確認是否有目錄指向/tmp，根據(jù)下面截圖得知/tmp目錄是mysql臨時表所在的目錄
 

       /tmp目錄16M，最多可以創(chuàng)建32個，這個量完全夠普通事物使用，需要具體定位什么情況需要這么大量的臨時表，以至于超過該大小，使用到磁盤空間。

三、mysql內(nèi)部臨時表（in-memory和on-disk）

3.1內(nèi)部臨時表的定義

• 不同于手工創(chuàng)建的臨時表，在sql執(zhí)行過程中可能會用到臨時文件存儲查詢結(jié)果，稱為internal temporary table；該過程由Mysql自動完成，用戶無法手工干預(yù)；

• 這些表或使用memory引擎存于內(nèi)存，或使用MyISAM引擎存于磁盤；

• 當(dāng)某些SQL命令在MySQL數(shù)據(jù)庫中被執(zhí)行的時候，它可能需要先創(chuàng)建一些內(nèi)部的臨時表來完成比較復(fù)雜的排序或分組查詢。MySQL的臨時表分為 in-memory 和 on-disk 兩種。 如有可能，MySQL 總是首先使用 in-memory 的臨時表， 而當(dāng)臨時表變得太大的時候，也可能被轉(zhuǎn)存為on-disk 的臨時表。

3.2內(nèi)部臨時表創(chuàng)建的源代碼展示

       MySQL源碼如下所示

五、內(nèi)部臨時表優(yōu)化以及建議

關(guān)于MySQL內(nèi)部臨時表（internal temporary table）的優(yōu)化：

• (1) 盡可能考慮如何避免SQL命令創(chuàng)建臨時表  

      對于一個查詢連接非常繁忙的數(shù)據(jù)庫，頻繁地使用需要創(chuàng)建臨時表的查詢本身就已經(jīng)是一個性能瓶頸。需要重新檢視您的數(shù)據(jù)表的結(jié)構(gòu)以及各表之間的關(guān)聯(lián)， 重新考慮主鍵和索引，重組數(shù)據(jù)結(jié)構(gòu)以減少應(yīng)用中需不同的ORDER BY和GROUP BY的情況。

      拆分為較少關(guān)聯(lián)層次的多次查詢，或使用View表。

      因為目前線上發(fā)生的環(huán)境已經(jīng)使用了view表，建議view表創(chuàng)建時候，根據(jù)業(yè)務(wù)需求，再創(chuàng)建，以減少 tmp消耗

• (2)盡量設(shè)法確保臨時表被創(chuàng)建于內(nèi)存而非磁盤之中  

      如果實在是無法避免創(chuàng)建臨時表，那么退而求其次，則需要盡量確保這些臨時表能夠被創(chuàng)建在內(nèi)存之中。避免在結(jié)構(gòu)設(shè)計和查詢命令中使用BLOB和TEXT類型字段，或可考慮用 SUBSTRRING(colum,length)函數(shù)將其轉(zhuǎn)換為字符串類型；用SQLSMALLRESULT選項通知數(shù)據(jù)庫使用in-memory臨時表；使用View來簡化查詢；使用RAM disk內(nèi)存盤來存儲MySQL 數(shù)據(jù)庫的臨時表（需確保無使用BLOB和TEXT字段）。  

• (3)如何避免 On-Disk Temporary Tables

  下面是官方說明：

 The best solution is to avoid using the BLOB and TEXT types unless you really need them.
  If you can't avoid them, you may be able to use the ORDER BY SUBSTRRING(colum,length) trick to convert the values to character strings. wihich will permit in-memory temporary tables. 
  Just be sure that you are using a short engough substring that the temporary table doesn't grow larger than max_heap_table_size or tmp_table_size, or MySQL will convert the table to an on-disk MyISAM table. 
  If the Extra column of EXPLAIN contains "Using temporary",the query uses an implicit temporary table. 
  --------------下面是中文翻譯
  最好的解決方案是避免使用BLOB和文本類型,除非你真的需要他們。如果無法避免它們,您可以使用ORDER BY SUBSTRRING(colum,length)將值轉(zhuǎn)換為字符串。將允許使用內(nèi)存臨時表。
  只是確保你使用的是短engough臨時表的子串，不用增長到超過max_heap_table_size tmp_table_size的大小，否則會創(chuàng)建一個磁盤MyISAM表
  如果解釋的額外的列包含“使用臨時”,查詢使用隱式臨時表。  

六、BLOB和TEXT導(dǎo)致的內(nèi)部磁盤臨時表說明

• 1、因為 Memory 存儲引擎不支持 BLOB和TEXT 類型，所以包含有 BLOB和TEXT 類型字段的查詢，當(dāng)它需要用到隱式臨時表的時候，就不得不使用 on-disk的MyISAM臨時表，即使它的查詢結(jié)果可能只有很簡單的幾行數(shù)據(jù)。

       這會導(dǎo)致一個嚴重的性能瓶頸，即使您能配置將MySQL的臨時表存儲到RAM disk上,依然還是會需要用到許多昂貴的操作系統(tǒng)的調(diào)用函數(shù)。 在實用中還發(fā)現(xiàn)，某些SQL語句的臨時表甚至根本連RAM disk都不能使用（此時SQL查詢命令會因為不能創(chuàng)建臨時表而失?。?/p>

• 2、BLOB和TEXT這兩個數(shù)據(jù)類型。這兩個數(shù)據(jù)類型都用來存儲大容量的數(shù)據(jù)。前者是采用二進制的形式來保存，而后者是采用字符形式來保存。

       這兩個數(shù)據(jù)類型與其他數(shù)據(jù)類型有本質(zhì)的不同。在MYSQL數(shù)據(jù)庫中，是將這兩個數(shù)據(jù)類型當(dāng)做有實體的對象來處理。存儲引擎也會采用特別的方式來保存他們。BLOB數(shù)據(jù)類型是采用二進制的方式來存儲數(shù)據(jù)。而采用二進制來存儲數(shù)據(jù)時，系統(tǒng)沒有字符集的要求，也不會設(shè)置排序規(guī)則。相反，TEXT采用字符形式來存儲數(shù)據(jù)，為此有字符集和排序規(guī)則的限制。

• 3、因為這兩種數(shù)據(jù)類型的容量比較大，為此對對這些類型的字段進行操作時，臨時表就會一下子變得很大。

       此時就很容易超過上面兩個參數(shù)的限制。系統(tǒng)就會將內(nèi)存臨時表轉(zhuǎn)換為磁盤臨時表。為此這兩種數(shù)據(jù)類型會增加產(chǎn)生磁盤臨時表的幾率。

• 4、不同的存儲類型對于數(shù)據(jù)類型的支持力度是不同的。

       如果某種存儲類型不支持某些數(shù)據(jù)類型，那么系統(tǒng)就會直接采用磁盤臨時表，即使數(shù)據(jù)沒有超過其規(guī)定的大小。

       簡單的說，就是對于存儲引擎，如果其不支持某些數(shù)據(jù)類型，那么對這些數(shù)據(jù)類型進行操作時，系統(tǒng)只能夠使用磁盤臨時表，而不能夠使用磁盤臨時表。如對于Memory存儲引擎來說，其不支持BLOB和TEXT數(shù)據(jù)類型。在系統(tǒng)運行中，如果使用了BLOB和TEXT列，并且需要隱式臨時表時，查詢將不會使用內(nèi)存臨時表，而直接采用磁盤臨時表。即使兩個數(shù)據(jù)類型中的列存儲的數(shù)據(jù)不多，也是如此。顯然這會大大的降低數(shù)據(jù)庫的性能。

網(wǎng)站標題：記一次生產(chǎn)MySQL服務(wù)器根目錄滿了的異常情況--DB臨時表使用異常-創(chuàng)新互聯(lián)
標題URL：http://weahome.cn/article/cojooi.html