补充六：mysql服务器设置之优化缓冲区

如需转载请标明出处：http://blog.csdn.net/itas109
QQ技术交流群：129518033

环境：
MySQL版本：5.5.15
操作系统：windows

1.读入缓冲区的大小

优化参数：read_buffer_size
参数作用：read_buffer_size是MySQL读入缓冲区的大小，将对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区，read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描非常频繁，并你认为频繁扫描进行的太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能，read_buffer_size变量控制这一提高表的顺序扫描的效率。

优化方法：
在mysql的配置文件my.ini中修改read_buffer_size参数。

[mysqld]

# Size of the buffer used for doing full table scans of MyISAM tables.
# Allocated per thread, if a full scan is needed.
read_buffer_size=4M

命令行格式	–read-buffer-size=#
系统变量	Name	read_buffer_size
系统变量	Scope	Global, Session
系统变量	Dynamic	Yes
允许值	Type	integer
允许值	Default	131072
允许值	Minimum	8200
允许值	Maximum	2147479552

 

 

 

 

 

 

注意：
数值设置应该是4KB的倍数，默认值为64K，最大值为2GB，建议值为4M。

 

2.随机读缓冲区大小

优化参数：read_rnd_buffer_size
参数作用：read_rnd_buffer_size是MySQL的随机读缓冲区大小，当按任意顺序读取行时将分配一个随机读取缓冲区，进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要大量数据可适当的调整该值，但MySQL会为每个客户连接分配该缓冲区所以尽量适当设置该值，以免内存开销过大。表的随机的顺序缓冲 提高读取的效率。
优化方法：
在mysql的配置文件my.ini中修改read_rnd_buffer_size参数。

[mysqld]

# Size of the buffer used for doing full table scans of MyISAM tables.
# Allocated per thread, if a full scan is needed.
read_rnd_buffer_size=16M

 

 

命令行格式	–read-buffer-size=#
系统变量	Name	read_rnd_buffer_size
系统变量	Scope	Global, Session
系统变量	Dynamic	Yes
允许值 (<= 5.5.2)	Type
允许值 (<= 5.5.2)	Default	262144
允许值 (<= 5.5.2)	Minimum	8200
允许值 (<= 5.5.2)	Maximum	2147483647
允许值 (>= 5.5.3)	Type	integer
允许值 (>= 5.5.3)	Default	262144
允许值 (>= 5.5.3)	Minimum	1
允许值 (>= 5.5.3)	Maximum	2147483647

 

 

 

 

 

 

 

 

 

注意：
该值可以极大提高ORDER BY性能， 默认值为256K，最大值为2GB，建议值为16M。

 

3.排序会话的缓存大小

优化参数：sort_buffer_size
参数作用：每个必须执行排序的会话都会分配一个这样大小的缓冲区。 sort_buffer_size并不特定于任何存储引擎，并以通用方式应用于优化。
优化方法：
在mysql的配置文件my.ini中修改sort_buffer_size参数。

[mysqld]

# This buffer is allocated when MySQL needs to rebuild the index in
# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE
# into an empty table. It is allocated per thread so be careful with
# large settings.
sort_buffer_size=32M

 

命令行格式	–sort-buffer-size=#
系统变量	Name	sort_buffer_size
系统变量	Scope	Global, Session
系统变量	Dynamic	Yes
允许值 (Windows)	Type	integer
允许值 (Windows)	Default	2097144
允许值 (Windows)	Minimum	32768
允许值 (Windows)	Maximum	4294967295
允许值 (Other, 32-bit platforms)	Type	integer
允许值 (Other, 32-bit platforms)	Default	2097144
允许值 (Other, 32-bit platforms)	Minimum	32768
允许值 (Other, 32-bit platforms)	Maximum	4294967295
允许值 (Other, 64-bit platforms)	Type	integer
允许值 (Other, 64-bit platforms)	Default	2097144
允许值 (Other, 64-bit platforms)	Minimum	32768
允许值 (Other, 64-bit platforms)	Maximum	18446744073709551615

 

 

 

 

 

 

 

 

 

 

 

 

注意：
sort_buffer_size的值也不会越大越好，过大的配置会消耗更多的内存。默认值为256K，最大值为4GB，建议值为32M。

 

4.索引块的缓冲区大小

优化参数：key_buffer_size
参数作用：索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。
优化方法：
在mysql的配置文件my.ini中修改key_buffer_size参数。

[mysqld]

# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size=128M

 

 

命令行格式	–key-buffer-size=#
系统变量	Name	key_buffer_size
系统变量	Scope	Global
系统变量	Dynamic	Yes
允许值 (32-bit platforms)	Type	integer
允许值 (32-bit platforms)	Default	8388608
允许值 (32-bit platforms)	Minimum	8
允许值 (32-bit platforms)	Maximum	4294967295
允许值 (64-bit platforms)	Type	integer
允许值 (64-bit platforms)	Default	8388608
允许值 (64-bit platforms)	Minimum	8
允许值 (64-bit platforms)	Maximum	OS_PER_PROCESS_LIMIT

 

 

 

 

 

 

 

 

 

注意：
默认值为8M，建议值为128M。

 

5.内部内存临时表的最大大小

优化参数：tmp_table_size
参数作用：内部内存临时表的最大大小。 该变量不适用于用户创建的MEMORY表。实际限制由tmp_table_size和max_heap_table_size中较小的值确定。 如果内存中的临时表超出限制，MySQL会自动将其转换为磁盘上的MyISAM表。 如果您执行了许多高级的GROUP BY查询，并且您拥有大量内存，请增加tmp_table_size的值（如有必要，请增大max_heap_table_size）。
优化方法：
在mysql的配置文件my.ini中修改tmp_table_size参数。

[mysqld]

# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size=128M

命令行格式	–tmp-table-size=#
系统变量	Name	tmp_table_size
系统变量	Scope	Global, Session
系统变量	Dynamic	Yes
允许值	Type	integer
允许值	Default	16777216
允许值	Minimum	1024
允许值	Maximum	18446744073709551615

 

 

 

 

 

 

注意：
tmp_table_size和max_heap_table_size一起限制了内部内存表的大小（二者中较小的值）。默认值为16M，建议值为128M。

 

6.可以复用的保存在中的线程的数量

优化参数：thread_cache_size
参数作用：服务器应该缓存多少个线程才能重用。 当客户端断开连接时，如果线程数少于thread_cache_size，客户端的线程将被放入缓存中。 如果可能的话，重用使用高速缓存的线程来满足线程请求，并且只有当高速缓存为空时才创建新的线程。 如果您有很多新的连接，可以增加此变量以提高性能。 通常情况下，如果你有一个好的线程实现，这不会提供显着的性能改进。 但是，如果您的服务器每秒看到数百个连接，则通常应将thread_cache_size设置为足够高，以便大多数新连接使用缓存线程。 通过检查Connections和Threads_created状态变量之间的区别，可以看到线程缓存的有效性。
优化方法：
在mysql的配置文件my.ini中修改thread_cache_size参数。

[mysqld]

# How many threads we should keep in a cache for reuse. When a client
# disconnects, the client's threads are put in the cache if there aren't
# more than thread_cache_size threads from before.  This greatly reduces
# the amount of thread creations needed if you have a lot of new
# connections. (Normally this doesn't give a notable performance
# improvement if you have a good thread implementation.)
thread_cache_size=120

命令行格式	–thread_cache_size=#
系统变量	Name	thread_cache_size
系统变量	Scope	Global, Session
系统变量	Dynamic	Yes
允许值	Type	integer
允许值	Default	0
允许值	Minimum	0
允许值	Maximum	16384

 

 

 

 

 

 

注意：
建议值为120。

 

7.缓存查询结果的内存量

优化参数：query_cache_size
参数作用：分配用于缓存查询结果的内存量。 默认值是0，禁用查询缓存。 为了显着降低开销，如果您不使用查询缓存，则还应该使用query_cache_type = 0启动服务器。允许的值是1024的倍数; 其他值向下舍入到最接近的倍数。 对于query_cache_size的非零值，即使query_cache_type = 0，也会分配很多字节的内存。
优化方法：
在mysql的配置文件my.ini中修改query_cache_size参数。

[mysqld]

# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the query
# cache enabled may result in significant speed improvements, if your
# have a lot of identical queries and rarely changing tables. See the
# "Qcache_lowmem_prunes" status variable to check if the current value
# is high enough for your load.
# Note: In case your tables change very often or if your queries are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
query_cache_size=64M

 

命令行格式	–query_cache_size=#
系统变量	Name	thread_cache_size
系统变量	Name	query_cache_size
系统变量	Scope	Global
系统变量	Dynamic	Yes
允许值 (32-bit platforms)	Type	integer
允许值 (32-bit platforms)	Default	0
允许值 (32-bit platforms)	Minimum	0
允许值 (32-bit platforms)	Maximum	4294967295
允许值 (64-bit platforms, <= 5.5.2)	Type	integer
允许值 (64-bit platforms, <= 5.5.2)	Default	0
允许值 (64-bit platforms, <= 5.5.2)	Minimum	0
允许值 (64-bit platforms, <= 5.5.2)	Maximum	18446744073709547520
允许值 (64-bit platforms, >= 5.5.3)	Type	integer
允许值 (64-bit platforms, >= 5.5.3)	Default	0
允许值 (64-bit platforms, >= 5.5.3)	Minimum	0
允许值 (64-bit platforms, >= 5.5.3)	Maximum	18446744073709551615

 

 

 

 

 

 

 

 

 

 

 

 

 

注意：
建议值为120。

 

8.所有线程的打开表的数量

优化参数：table_open_cache
参数作用：所有线程的打开表的数量。增加此值会增加mysqld所需的文件描述符的数量。 您可以通过检查Opened_tables状态变量来检查是否需要增加表缓存。如果Opened_tables的值很大，而且你经常不使用FLUSH TABLES（这只是强制关闭并重新打开所有的表），那么你应该增加table_open_cache变量的值。
优化方法：
在mysql的配置文件my.ini中修改table_open_cache参数。（配置文件中可能叫做table_cache）

[mysqld]

# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
## table_cache=256
table_open_cache=1024

 

系统变量	Name	table_open_cache
系统变量	Scope	Global
系统变量	Dynamic	Yes
允许值	Type	integer
允许值	Default	400
允许值	Minimum	1
允许值	Maximum	524288

 

 

 

 

 

注意：
在MySQL 5.1.3之前table_open_cache叫做 table_cache。
建议设置数值为max_connections
　　查询table_open_cache设置数目

show variables like '%table_open_cache%';

 

 

查询打开表的数目

show global status like 'open%tables%';

 

如果Open_tables的值已经接近table_open_cache的值，且Opened_tables还在不断变大，则说明mysql正在将缓存的表释放以容纳新的表，此时可能需要加大table_open_cache的值。对于大多数情况，比较适合的值： 

Open_tables / Opened_tables >= 0.85
Open_tables / table_open_cache<= 0.95

如果对此参数的把握不是很准，VPS管理百科给出一个很保守的设置建议：把MySQL数据库放在生产环境中试运行一段时间，然后把参数的值调整得比Opened_tables的数值大一些，并且保证在比较高负载的极端条件下依然比Opened_tables略大。