mysql用於分配記憶體的引數

除了常見的key_buffer_size/innodb_buffer_pool/query_cache_size以外，還有一些個容易被忽略的引數；

它們或對特定操作有重要影響，或在某些情況下佔有過量記憶體，從而導致一些無法預料又很難檢測的狀況；

 

Thread_stack：預設256KB，如果sql過於複雜，比如發生大量遞迴呼叫，可能需要更大的值，否則會出現”thread stack overrun：…”；在AMD64平臺上有相應BUG，如無特別需求，建議不要手工設定；

 

Net_buffer_length：每個執行緒都有一個connection buffer和result buffer，預設為此值；但可以根據實際需求自動調整，上限為max_allowed_packet，使用完畢後會自動回落；只能在global級別調整，預設16k最大1M；

 

Read_buffer_size：順序掃描MyISAM表時為每個表分配的快取，以4k為基本單位，預設128K；對於所有引擎，以下3個場景也適用：

使用order by排序時，快取臨時檔案中的索引；向分割槽表批次插入資料；快取nested查詢的結果集；

 

此引數不只影響順序讀，而且還影響寫操作：將順序寫快取起來直至=min(read_buffer_size,8k)才開始物理寫，適用於select into … outfile，以及filesort—當merge result寫向臨時檔案時

http://venublog.com/2010/06/23/how-read_buffer_size-impacts-write-buffering-and-write-performance/

 

此引數不應大於max_allowed_packet，否則可能導致replication中斷，slave報錯” log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master”

http://www.mysqlperformanceblog.com/2012/06/06/read_buffer_size-can-break-your-replication/

 

Read_rnd_buffer_size：官方文件定義為When reading rows from a MyISAM table in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks

當使用傳統的雙路檔案排序時，在第2次讀時用於快取資料；

 

Join_buffer_size：用於索引範圍掃描或執行全表掃描的join操作，http://dev.mysql.com/doc/refman/5.5/en/nested-loop-joins.html

 

Sort_buffer_size：如果sort_merge_passes偏高，則考慮調大此引數，以加速group by/order by，但即使執行緒用不完也會 全部分配，故需要謹慎操作；

 

Thread_cache_size：最大可快取的thread數量，通常每個佔有256K，不會消耗太多記憶體

 

Bulk_insert_buffer_size：預設8M，當對MyISAM非空表執行insert … select/ insert … values(…),(…)或者load data infile時，使用樹狀cache快取資料，每個thread分配一個；

注：當對MyISAM表load 大檔案時，調大bulk_insert_buffer_size/myisam_sort_buffer_size/key_buffer_size會極大提升速度

http://venublog.com/2007/11/07/load-data-infile-performance/

 

Myisam_sort_buffer_size：當對MyISAM表執行repair table或建立索引時，用以快取排序索引；設定太小時可能會遇到” myisam_sort_buffer_size is too small”

 

Myisam_max_sort_file_size：當對MyISAM表重建索引時(repair/alter table/load data infile)，允許使用的臨時檔案最大值；如果超過此限制索引建立則改用key cache，此時show processlist會顯示該執行緒處於”repair with keycache”而非”repair by sorting”，前者逐條建立索引記錄；另外，當指定的tmpdir目錄空間不足時也會導致類似情形；

 

Max_length_for_sort_data：決定file sort時使用哪種演算法，如果返回列的長度總和小於此，則使用最新的單路排序；

 

Max_heap_table_size：memory表的最大大小

Temp_table_size：sql執行過程中生成的臨時檔案(記憶體)的大小，如果大於min(tmp_table_size, max_heap_table_size)或使用到blob/text，則改用磁碟儲存；

 

Open_table_cache：儲存開啟的檔案描述符，每個執行緒一個；5.1之前為table_cache，包含 檔案描述符和frm檔案，5.1後拆分成兩個引數；

Table_definition_cache：儲存frm檔案，不同於open_table_cache的per-thread，這些檔案可被所有執行緒共享；5.1引入；

與MyISAM不同，InnoDB的open table和open file並無直接聯絡，即開啟frm表時其相應的ibd檔案可能處於關閉狀態；

故InnoDB只會用到table_definiton_cache，不會使用table_open_cache；

其frm檔案儲存於table_definition_cache中，而idb則由innodb_open_files決定(前提是開啟了innodb_file_per_table)；

除此之外，innodb還在記憶體中維護一個data dictionary，用於記錄所有訪問過的表，即便表已經關閉了也不釋放，show innodb status會顯示該部分記憶體大小；

 

如果這兩個引數設定過大，有可能會消耗較多記憶體

 

 

除此之外，還有一些由grant/create user等語句佔有的記憶體，只分配不釋放，可定期呼叫flush privileges回收；

 

Innodb的innodb_buffer_pool的額外開銷

http://space.itpub.net/15480802/viewspace-755616

Mysql用於快取的記憶體

http://space.itpub.net/15480802/viewspace-755582