简单实现MySQL服务器的优化配置方法
程序员文章站
2023-12-18 08:19:46
公司网站访问量越来越大，mysql自然成为瓶颈，因此最近我一直在研究 mysql 的优化，第一步自然想到的是 mysql 系统参数的优化，作为一个访问量很大的网站（日20万...
公司网站访问量越来越大，mysql自然成为瓶颈，因此最近我一直在研究 mysql 的优化，第一步自然想到的是 mysql 系统参数的优化，作为一个访问量很大的网站（日20万人次以上）的数据库系统，不可能指望 mysql 默认的系统参数能够让 mysql运行得非常顺畅。以下的文章主要介绍的是对mysql服务器的优化配置的时机操作步骤，同时本文也介绍了mysql服务器的优化配置的实际应用代码，如果你对其相关的实际应用感兴趣的话，你就可以点击以下的文章对其进行了解。
你能用这个命令得到mysql服务器缺省缓冲区大小：
shell> mysql(和php搭配之最佳组合)d --help
这个命令生成一张所有mysql(和php搭配之最佳组合)d选项和可配置变量的表。输出包括缺省值并且看上去象这样一些东西：
possible variables for option --set-variable (-o) are:
back_log current value: 5
connect_timeout current value: 5
delayed_insert_timeout current value: 300
delayed_insert_limit current value: 100
delayed_queue_size current value: 1000
flush_time current value: 0
interactive_timeout current value: 28800
join_buffer_size current value: 131072
key_buffer_size current value: 1048540
lower_case_table_names current value: 0
long_query_time current value: 10
max_allowed_packet current value: 1048576
max_connections current value: 100
max_connect_errors current value: 10
max_delayed_threads current value: 20
max_heap_table_size current value: 16777216
max_join_size current value: 4294967295
max_sort_length current value: 1024
max_tmp_tables current value: 32
max_write_lock_count current value: 4294967295
net_buffer_length current value: 16384
query_buffer_size current value: 0
record_buffer current value: 131072
sort_buffer current value: 2097116
table_cache current value: 64
thread_concurrency current value: 10
tmp_table_size current value: 1048576
thread_stack current value: 131072
wait_timeout current value: 28800
如果有一个mysql服务器正在运行，通过执行这个命令，你可以看到它实际上使用的变量的值：
shell> mysql(和php搭配之最佳组合)admin variables
每个选项在下面描述。对于缓冲区大小、长度和栈大小的值以字节给出，你能用于个后缀“k”或“m” 指出以k字节或兆字节显示值。例如，16m指出16兆字节。后缀字母的大小写没有关系；16m和16m是相同的。
你也可以用命令show status自一个运行的服务器看见一些统计。见7.21 show语法(得到表、列的信息)。
back_log
要求mysql(和php搭配之最佳组合)能有的连接数量。当主要mysql(和php搭配之最佳组合)线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在mysql(和php搭配之最佳组合)暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的tcp/ip连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 unix listen(2)系统调用的手册页应该有更多的细节。检查你的os文档找出这个变量的最大值。试图设定back_log高于你的操作系统的限制将是无效的。
connect_timeout
mysql(和php搭配之最佳组合)d服务器在用bad handshake（糟糕的握手）应答前正在等待一个连接报文的秒数。
delayed_insert_timeout
一个insert delayed线程应该在终止之前等待insert语句的时间。
delayed_insert_limit
在插入delayed_insert_limit行后，insert delayed处理器将检查是否有任何select语句未执行。如果这样，在继续前执行允许这些语句。
delayed_queue_size
应该为处理insert delayed分配多大一个队列(以行数)。如果排队满了，任何进行insert delayed的客户将等待直到队列又有空间了。
flush_time
如果这被设置为非零值，那么每flush_time秒所有表将被关闭(以释放资源和sync到磁盘)。
interactive_timeout
mysql服务器在关上它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对mysql(和php搭配之最佳组合)_real_connect()使用client_interactive选项的客户。也可见wait_timeout。
join_buffer_size
用于全部联结(join)的缓冲区大小(不是用索引的联结)。缓冲区对2个表间的每个全部联结分配一次缓冲区，当增加索引不可能时，增加该值可得到一个更快的全部联结。（通常得到快速联结的最佳方法是增加索引。）
key_buffer_size
索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。记住既然mysql(和php搭配之最佳组合)不缓存读取的数据，你将必须为os文件系统缓存留下一些空间。为了在写入多个行时得到更多的速度，使用lock tables。见7.24lock tables/unlock tables语法。
long_query_time
如果一个查询所用时间超过它(以秒计)，slow_queries记数器将被增加。
max_allowed_packet
一个包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节，但是可在需要时增加到max_allowed_packet个字节。缺省地，该值太小必能捕捉大的(可能错误)包。如果你正在使用大的blob列，你必须增加该值。它应该象你想要使用的最大blob的那么大。
max_connections
允许的同时客户的数量。增加该值增加mysql(和php搭配之最佳组合)d要求的文件描述符的数量。见下面对文件描述符限制的注释。见18.2.4 too many connections错误。
max_connect_errors
如果有多于该数量的从一台主机中断的连接，这台主机阻止进一步的连接。你可用flush hosts命令疏通一台主机。
max_delayed_threads
不要启动多于的这个数字的线程来处理insert delayed语句。如果你试图在所有insert delayed线程在用后向一张新表插入数据，行将被插入，就像delayed属性没被指定那样。
max_join_size
可能将要读入多于max_join_size个记录的联结将返回一个错误。如果你的用户想要执行没有一个where子句、花很长时间并且返回百万行的联结，设置它。
max_sort_length
在排序blob或text值时使用的字节数(每个值仅头max_sort_length个字节被使用；其余的被忽略)。
max_tmp_tables
（该选择目前还不做任何事情)。一个客户能同时保持打开的临时表的最大数量。
net_buffer_length
通信缓冲区在查询之间被重置到该大小。通常这不应该被改变，但是如果你有很少的内存，你能将它设置为查询期望的大小。（即，客户发出的sql语句期望的长度。如果语句超过这个长度，缓冲区自动地被扩大，直到max_allowed_packet个字节。）
record_buffer
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。
sort_buffer
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速order by或group by操作。见18.5 mysql(和php搭配之最佳组合)在哪儿存储临时文件。
table_cache
为所有线程打开表的数量。增加该值能增加mysql(和php搭配之最佳组合)d要求的文件描述符的数量。mysql(和php搭配之最佳组合)对每个唯一打开的表需要2个文件描述符，见下面对文件描述符限制的注释。对于表缓存如何工作的信息，见10.2.4 mysql(和php搭配之最佳组合)怎样打开和关闭表。
tmp_table_size
如果一张临时表超出该大小，mysql(和php搭配之最佳组合)产生一个the table tbl_name is full形式的错误，如果你做很多高级group by查询，增加tmp_table_size值。
thread_stack
每个线程的栈大小。由crash-me测试检测到的许多限制依赖于该值。缺省队一般的操作是足够大了。见10.8 使用你自己的基准。
wait_timeout
mysql服务器在关闭它之前在一个连接上等待行动的秒数。也可见interactive_timeout。
mysql(和php搭配之最佳组合)使用是很具伸缩性的算法，因此你通常能用很少的内存运行或给mysql(和php搭配之最佳组合)更多的被存以得到更好的性能。
如果你有很多内存和很多表并且有一个中等数量的客户，想要最大的性能，你应该一些象这样的东西：
shell> safe_mysql(和php搭配之最佳组合)d -o key_buffer=16m -o table_cache=128 \ -o sort_buffer=4m -o record_buffer=1m &
如果你有较少的内存和大量的连接，使用这样一些东西：
shell> safe_mysql(和php搭配之最佳组合)d -o key_buffer=512k -o sort_buffer=100k \ -o record_buffer=100k &
或甚至：
shell> safe_mysql(和php搭配之最佳组合)d -o key_buffer=512k -o sort_buffer=16k \ -o table_cache=32 -o record_buffer=8k -o net_buffer=1k &
如果有很多连接，“交换问题”可能发生，除非mysql(和php搭配之最佳组合)d已经被配置每个连接使用很少的内存。当然如果你对所有连接有足够的内存，mysql(和php搭配之最佳组合)d执行得更好。
注意，如果你改变mysql(和php搭配之最佳组合)d的一个选项，它实际上只对mysql服务器的那个例子保持。
为了明白一个参数变化的效果，这样做：
shell> mysql(和php搭配之最佳组合)d -o key_buffer=32m --help
保证--help选项是最后一个；否则，命令行上在它之后列出的任何选项的效果将不在反映在输出中。
你能用这个命令得到mysql服务器缺省缓冲区大小：
shell> mysql(和php搭配之最佳组合)d --help
这个命令生成一张所有mysql(和php搭配之最佳组合)d选项和可配置变量的表。输出包括缺省值并且看上去象这样一些东西：
possible variables for option --set-variable (-o) are:
复制代码 代码如下:
back_log current value: 5
connect_timeout current value: 5
delayed_insert_timeout current value: 300
delayed_insert_limit current value: 100
delayed_queue_size current value: 1000
flush_time current value: 0
interactive_timeout current value: 28800
join_buffer_size current value: 131072
key_buffer_size current value: 1048540
lower_case_table_names current value: 0
long_query_time current value: 10
max_allowed_packet current value: 1048576
max_connections current value: 100
max_connect_errors current value: 10
max_delayed_threads current value: 20
max_heap_table_size current value: 16777216
max_join_size current value: 4294967295
max_sort_length current value: 1024
max_tmp_tables current value: 32
max_write_lock_count current value: 4294967295
net_buffer_length current value: 16384
query_buffer_size current value: 0
record_buffer current value: 131072
sort_buffer current value: 2097116
table_cache current value: 64
thread_concurrency current value: 10
tmp_table_size current value: 1048576
thread_stack current value: 131072
wait_timeout current value: 28800
如果有一个mysql服务器正在运行，通过执行这个命令，你可以看到它实际上使用的变量的值：
复制代码 代码如下:
shell> mysql(和php搭配之最佳组合)admin variables
每个选项在下面描述。对于缓冲区大小、长度和栈大小的值以字节给出，你能用于个后缀“k”或“m” 指出以k字节或兆字节显示值。例如，16m指出16兆字节。后缀字母的大小写没有关系；16m和16m是相同的。
你也可以用命令show status自一个运行的服务器看见一些统计。见7.21 show语法(得到表、列的信息)。
back_log
要求mysql(和php搭配之最佳组合)能有的连接数量。当主要mysql(和php搭配之最佳组合)线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在mysql(和php搭配之最佳组合)暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的tcp/ip连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 unix listen(2)系统调用的手册页应该有更多的细节。检查你的os文档找出这个变量的最大值。试图设定back_log高于你的操作系统的限制将是无效的。
connect_timeout
mysql(和php搭配之最佳组合)d服务器在用bad handshake（糟糕的握手）应答前正在等待一个连接报文的秒数。
delayed_insert_timeout
一个insert delayed线程应该在终止之前等待insert语句的时间。
delayed_insert_limit
在插入delayed_insert_limit行后，insert delayed处理器将检查是否有任何select语句未执行。如果这样，在继续前执行允许这些语句。
delayed_queue_size
应该为处理insert delayed分配多大一个队列(以行数)。如果排队满了，任何进行insert delayed的客户将等待直到队列又有空间了。
flush_time
如果这被设置为非零值，那么每flush_time秒所有表将被关闭(以释放资源和sync到磁盘)。
interactive_timeout
mysql服务器在关上它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对mysql(和php搭配之最佳组合)_real_connect()使用client_interactive选项的客户。也可见wait_timeout。
join_buffer_size
用于全部联结(join)的缓冲区大小(不是用索引的联结)。缓冲区对2个表间的每个全部联结分配一次缓冲区，当增加索引不可能时，增加该值可得到一个更快的全部联结。（通常得到快速联结的最佳方法是增加索引。）
key_buffer_size
索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。记住既然mysql(和php搭配之最佳组合)不缓存读取的数据，你将必须为os文件系统缓存留下一些空间。为了在写入多个行时得到更多的速度，使用lock tables。见7.24lock tables/unlock tables语法。
long_query_time
如果一个查询所用时间超过它(以秒计)，slow_queries记数器将被增加。
max_allowed_packet
一个包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节，但是可在需要时增加到max_allowed_packet个字节。缺省地，该值太小必能捕捉大的(可能错误)包。如果你正在使用大的blob列，你必须增加该值。它应该象你想要使用的最大blob的那么大。
max_connections
允许的同时客户的数量。增加该值增加mysql(和php搭配之最佳组合)d要求的文件描述符的数量。见下面对文件描述符限制的注释。见18.2.4 too many connections错误。
max_connect_errors
如果有多于该数量的从一台主机中断的连接，这台主机阻止进一步的连接。你可用flush hosts命令疏通一台主机。
max_delayed_threads
不要启动多于的这个数字的线程来处理insert delayed语句。如果你试图在所有insert delayed线程在用后向一张新表插入数据，行将被插入，就像delayed属性没被指定那样。
max_join_size
可能将要读入多于max_join_size个记录的联结将返回一个错误。如果你的用户想要执行没有一个where子句、花很长时间并且返回百万行的联结，设置它。
max_sort_length
在排序blob或text值时使用的字节数(每个值仅头max_sort_length个字节被使用；其余的被忽略)。
max_tmp_tables
（该选择目前还不做任何事情)。一个客户能同时保持打开的临时表的最大数量。
net_buffer_length
通信缓冲区在查询之间被重置到该大小。通常这不应该被改变，但是如果你有很少的内存，你能将它设置为查询期望的大小。（即，客户发出的sql语句期望的长度。如果语句超过这个长度，缓冲区自动地被扩大，直到max_allowed_packet个字节。）
record_buffer
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。
sort_buffer
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速order by或group by操作。见18.5 mysql(和php搭配之最佳组合)在哪儿存储临时文件。
table_cache
为所有线程打开表的数量。增加该值能增加mysql(和php搭配之最佳组合)d要求的文件描述符的数量。mysql(和php搭配之最佳组合)对每个唯一打开的表需要2个文件描述符，见下面对文件描述符限制的注释。对于表缓存如何工作的信息，见10.2.4 mysql(和php搭配之最佳组合)怎样打开和关闭表。
tmp_table_size
如果一张临时表超出该大小，mysql(和php搭配之最佳组合)产生一个the table tbl_name is full形式的错误，如果你做很多高级group by查询，增加tmp_table_size值。
thread_stack
每个线程的栈大小。由crash-me测试检测到的许多限制依赖于该值。缺省队一般的操作是足够大了。见10.8 使用你自己的基准。
wait_timeout
mysql服务器在关闭它之前在一个连接上等待行动的秒数。也可见interactive_timeout。
mysql(和php搭配之最佳组合)使用是很具伸缩性的算法，因此你通常能用很少的内存运行或给mysql(和php搭配之最佳组合)更多的被存以得到更好的性能。
如果你有很多内存和很多表并且有一个中等数量的客户，想要最大的性能，你应该一些象这样的东西：
复制代码 代码如下:
shell> safe_mysql(和php搭配之最佳组合)d -o key_buffer=16m -o table_cache=128 \ -o sort_buffer=4m -o record_buffer=1m &
如果你有较少的内存和大量的连接，使用这样一些东西：
复制代码 代码如下:
shell> safe_mysql(和php搭配之最佳组合)d -o key_buffer=512k -o sort_buffer=100k \ -o record_buffer=100k &
或甚至：
复制代码 代码如下:
shell> safe_mysql(和php搭配之最佳组合)d -o key_buffer=512k -o sort_buffer=16k \ -o table_cache=32 -o record_buffer=8k -o net_buffer=1k &
如果有很多连接，“交换问题”可能发生，除非mysql(和php搭配之最佳组合)d已经被配置每个连接使用很少的内存。当然如果你对所有连接有足够的内存，mysql(和php搭配之最佳组合)d执行得更好。
注意，如果你改变mysql(和php搭配之最佳组合)d的一个选项，它实际上只对mysql服务器的那个例子保持。
为了明白一个参数变化的效果，这样做：
复制代码 代码如下:
shell> mysql(和php搭配之最佳组合)d -o key_buffer=32m --help
保证--help选项是最后一个；否则，命令行上在它之后列出的任何选项的效果将不在反映在输出中。