mysql用于分配内存的参数

除了常见的key_buffer_size/innodb_buffer_pool/query_cache_size以外，还有一些个容易被忽略的参数；

它们或对特定操作有重要影响，或在某些情况下占有过量内存，从而导致一些无法预料又很难检测的状况；

 

Thread_stack：默认256KB，如果sql过于复杂，比如发生大量递归调用，可能需要更大的值，否则会出现”thread stack overrun：…”；在AMD64平台上有相应BUG，如无特别需求，建议不要手工设置；

https://bugs.launchpad.net/ubuntu/+source/mysql-5.5/+bug/650936

 

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会显示该部分内存大小；

 

如果这两个参数设置过大，有可能会消耗较多内存

http://www.taobaodba.com/html/1367_mysql_memory_exhausted.html/comment-page-1#comment-19089

 

 

除此之外，还有一些由grant/create user等语句占有的内存，只分配不释放，可定期调用flush privileges回收；

 

Innodb的innodb_buffer_pool的额外开销

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

Mysql用于缓存的内存

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

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-757509/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15480802/viewspace-757509/