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%";
"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 ofINSERT
Statements”.
-
myisam_sort_buffer_size
The size of the buffer that is allocated when sorting
//不是给修改index的时候使用的...MyISAM
indexes during aREPAIR TABLE
or when creating indexes withCREATE INDEX
orALTER TABLE
.
-
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 cacheWhen tuning a MySQL server, the two most important variables to configure are
key_buffer_size
andtable_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/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 improve
//When reading rows in sorted order following a key-sorting operation -- 意思是order by的那些列,正好该列的索引排序就是order by的顺序(explain 当中type是index),所以不需要在额外的进行排序操作,增大这个buffer,把索引上的内容都按序写到这里,然后在顺序的读,貌似给MyISAM使用ORDER 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.
-
sort_buffer_size
Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster
这个给力,能够加速mysql的sort过程ORDER BY
orGROUP BY
operations. See Section A.4.4, “Where MySQL Stores Temporary Files”.
待续