mysql 几个buffer 和 cache参数

show variables like "%buffer%";

大概得到下面几个东西

Variable Name
"innodb_log_buffer_size"
"join_buffer_size"
"key_buffer_size"
"myisam_sort_buffer_size"
"net_buffer_length"
"preload_buffer_size"
"read_buffer_size"
"read_rnd_buffer_size"
"sort_buffer_size"
"sql_buffer_result"

show variables like "%cache%";

"Variable_name"

"binlog_cache_size"
"have_query_cache"
"key_cache_age_threshold"
"key_cache_block_size"
"key_cache_division_limit"
"max_binlog_cache_size"
"query_cache_limit"
"query_cache_min_res_unit"
"query_cache_size"
"query_cache_type"
"query_cache_wlock_invalidate"
"table_definition_cache"
"table_open_cache"
"thread_cache_size"

好像mysql 里面并不是buffer针对于写 + cache 针对于读，好像这2个概念有点混用，下面介绍几个挺重要的buffer & cache

• join_buffer_size

• The size of the buffer that is used for joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary. 

  
  

  Increase the value to get better index handling (for all reads and multiple writes) to as much as you can afford. Using a value that is 25% of total memory on a machine that mainly runs MySQL is quite common. However, if you make the value too large (for example, more than 50% of your total memory) your system might start to page and become extremely slow. MySQL relies on the operating system to perform filesystem caching for data reads, so you must leave some room for the filesystem cache. Consider also the memory requirements of other storage engines.

  For even more speed when writing many rows at the same time, use LOCK TABLES. See Section 7.2.16, “Speed of INSERT Statements”. 

  
  

  
  

• myisam_sort_buffer_size

  The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE. 

  //不是给修改index的时候使用的...

  
  

• key_buffer_size

  Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache

  When tuning a MySQL server, the two most important variables to configure are key_buffer_size and table_open_cache. You should first feel confident that you have these set appropriately before trying to change any other variables.

  The following examples indicate some typical variable values for different runtime configurations. 

  //这个才是真正的index buffer, 不过貌似只是给MyISAM使用
  
  
  

  
  

• read_buffer_size

  Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value, which defaults to 131072. 

  //顺序scan（做全表扫描的时候），需要用到这个， 貌似也只是给MyISAM用的 from http://www.mysqlperformanceblog.com/2007/09/17/mysql-what-read_buffer_size-value-is-optimal/
  peter  says: Sinisa, As far as I remember Innodb tables are not read using read_buffer， 也就是说innodb 是不用这个参数的，而且也不是调得越大越好，这里是别人的测试结果
  
  

  read_buffer_size impace on scan performance

  read_buffer_size	Time (sec)
  8200	45.2
  16K	44.8
  32K	45.6
  64K	43.4
  128K	43.0
  256K	51.9
  512K	60.8
  2M	65.2
  8M	66.8
  32M	67.2

  8200 bytes is the minimum size for read_buffer_size, this is why we start from this value.

  As you can see results look really strange. Performance indeed grows by few percent as you increase buffer to 128K but after that instead of improving any further it drops down sharply being 50% slower at 2MB size. After this value it continues to drop slowly all the way to 32M.

  Why this is happening ? I have not spent enough time to come up with good explanation. It could be OS has to split large requests into multiple ones submitting them to device which slows things down or it could be something else. But the fact remains – on some platforms for some workloads large read_buffer_sizes may hurt you even on large full table scans. (I wroteabout some other cases when it hurts a while ago)

  
  

  
  

  
  

• read_rnd_buffer_size

  When reading rows in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks. Setting the variable to a large value can improveORDER BY performance by a lot. However, this is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries. 

  //When reading rows in sorted order following a key-sorting operation -- 意思是order by的那些列，正好该列的索引排序就是order by的顺序（explain 当中type是index），所以不需要在额外的进行排序操作，增大这个buffer，把索引上的内容都按序写到这里，然后在顺序的读，貌似给MyISAM使用

  
  

• sort_buffer_size

  Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations. See Section A.4.4, “Where MySQL Stores Temporary Files”. 

  这个给力，能够加速mysql的sort过程

  
  

  待续