MySQL記憶體執行緒獨享使用的方法

在   中,執行緒獨享記憶體主要用於各客戶端連線執行緒儲存各種操作的獨享資料,如執行緒棧資訊,分組排序操作,資料讀寫緩衝,結果集暫存等等,而且大多數可以透過相關引數來控制記憶體的使用量。

 執行緒棧資訊使用記憶體(thread_stack):主要用來存放每一個執行緒自身的標識資訊,如執行緒id,執行緒執行時基本資訊等等,我們可以透過 thread_stack 引數來設定為每一個執行緒棧分配多大的記憶體。

 排序使用記憶體(sort_buffer_size):MySQL 用此記憶體區域進行排序操作(filesort),完成客戶端的排序請求。當我們設定的排序區快取大小無法滿足排序實際所需記憶體的時候,MySQL 會將資料寫入磁碟檔案來完成排序。由於磁碟和記憶體的讀寫效能完全不在一個數量級,所以sort_buffer_size引數對排序操作的效能影響絕對不可小視。排序操作的實現原理請參考:MySQL Order By 的實現分析。

 Join操作使用記憶體(join_buffer_size):應用程式經常會出現一些兩表(或多表)Join的操作需求,MySQL在完成某些 Join 需求的時候(all/index join),為了減少參與Join的“被驅動表”的讀取次數以提高效能,需要使用到 Join Buffer 來協助完成 Join操作(具體 Join 實現 演算法請參考:MySQL 中的 Join 基本實現原理)。當 Join Buffer 太小,MySQL 不會將該 Buffer 存入磁碟檔案,而是先將Join Buffer中的結果集與需要 Join 的表進行 Join 操作,然後清空 Join Buffer 中的資料,繼續將剩餘的結果集寫入此 Buffer 中,如此往復。這勢必會造成被驅動表需要被多次讀取,成倍增加 IO 訪問,降低效率。

 順序讀取資料緩衝區使用記憶體(read_buffer_size):這部分記憶體主要用於當需要順序讀取資料的時候,如無發使用索引的情況下的全表掃描,全索引掃描等。在這種時候,MySQL 按照資料的儲存順序依次讀取資料塊,每次讀取的資料快首先會暫存在read_buffer_size中,當 buffer 空間被寫滿或者全部資料讀取結束後,再將buffer中的資料返回給上層呼叫者,以提高效率。

 隨機讀取資料緩衝區使用記憶體(read_rnd_buffer_size):和順序讀取相對應,當 MySQL 進行非順序讀取(隨機讀取)資料塊的時候,會利用這個緩衝區暫存讀取的資料。如根據索引資訊讀取表資料,根據排序後的結果集與表進行Join等等。總的來說,就是當資料塊的讀取需要滿足一定的順序的情況下,MySQL 就需要產生隨機讀取,進而使用到 read_rnd_buffer_size 引數所設定的記憶體緩衝區。

 連線資訊及返回客戶端前結果集暫存使用記憶體(net_buffer_size):這部分用來存放客戶端連線執行緒的連線資訊和返回客戶端的結果集。當 MySQL 開始產生可以返回的結果集,會在透過網路返回給客戶端請求執行緒之前,會先暫存在透過 net_buffer_size 所設定的緩衝區中,等滿足一定大小的時候才開始向客戶端傳送,以提高網路傳輸效率。不過,net_buffer_size 引數所設定的僅僅只是該快取區的初始化大小,MySQL 會根據實際需要自行申請更多的記憶體以滿足需求,但最大不會超過 max_allowed_packet 引數大小。

 批次插入暫存使用記憶體(bulk_insert_buffer_size):當我們使用如 insert …values(…),(…),(…)… 的方式進行批次插入的時候,MySQL 會先將提交的資料放如一個快取空間中,當該快取空間被寫滿或者提交完所有資料之後,MySQL 才會一次性將該快取空間中的資料寫入資料庫並清空快取。此外,當我們進行 LOAD DATA INFILE 操作來將文字檔案中的資料 Load 進資料庫的時候,同樣會使用到此緩衝區。

 臨時表使用記憶體(tmp_table_size):當我們進行一些特殊操作如需要使用臨時表才能完成的 Order By,Group By 等等,MySQL 可能需要使用到臨時表。當我們的臨時表較小(小於 tmp_table_size 引數所設定的大小)的時候,MySQL 會將臨時表建立成記憶體臨時表,只有當 tmp_table_size 所設定的大小無法裝下整個臨時表的時候,MySQL 才會將該表建立成 MyISAM 儲存引擎的表存放在磁碟上。不過,當另一個系統引數 max_heap_table_size 的大小還小於 tmp_table_size 的時候,MySQL 將使用 max_heap_table_size 引數所設定大小作為最大的記憶體臨時表大小,而忽略 tmp_table_size 所設定的值。而且 tmp_table_size 引數從 MySQL 5.1.2 才開始有,之前一直使用 max_heap_table_size。

 上面所列舉的 MySQL 執行緒獨享記憶體僅僅只是所有執行緒獨享記憶體中的部分,並不是全部,選擇的原則是可能對 MySQL 的效能產生較大的影響,且可以透過系統引數進行調節。

 由於以上記憶體都是執行緒獨享,極端情況下的記憶體總體使用量將是所有連線執行緒的總倍數。所以各位朋友在設定過程中一定要謹慎,切不可為了提升效能就盲目的增大各引數值,避免因為記憶體不夠而產生 Out Of Memory 異常或者是嚴重的 Swap 交換反而降低整體效能。