4G内存服务器的MySQL配置优化

412次阅读

共计 2528 个字符，预计需要花费 7 分钟才能阅读完成。

公司网站访问量越来越大（日均超 10 万 PV），MySQL 自然成为瓶颈，关于 MySQL 的优化，最基本的是 MySQL 系统参数的优化。

MySQL 对于 web 架构性能的影响最大，也是关键的核心部分。MySQL 的设置是否合理优化，直接影响到 web 的速度和承载量！同时，MySQL 也是优化难度最大的一个部分，不但需要理解一些 MySQL 专业知识，同时还需要长时间的观察统计并且根据经验进行判断，然后设置合理的参数。下面我们了解一下 MySQL 优化的一些基础，MySQL 自身 (my.cnf) 的优化。

我们介绍一些对性能影响较大的参数。由于 my.cnf 文件的优化设置是与服务器硬件配置息息相关的，因而我们指定一个常见 2U 的服务器硬件环境：CPU: 2 颗 Intel Xeon 2.4GHz 内存: 4GB DDR 硬盘: SCSI。

下面，我们根据以上硬件配置结合一份已经优化好的 my.cnf 进行说明：

[mysqld]
port = 3306
serverid = 1
socket = /tmp/mysql.sock
skip-locking
# 避免 MySQL 的外部锁定，减少出错几率增强稳定性。
skip-name-resolve
# 禁止 MySQL 对外部连接进行 DNS 解析，使用这一选项可以消除 MySQL 进行 DNS 解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用 IP 地址方式，否则 MySQL 将无法正常处理连接请求！
back_log = 384
#back_log 参数的值指出在 MySQL 暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的 TCP/IP 连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自己的限制。试图设定 back_log 高于你的操作系统的限制将是无效的。默认值为 50。对于 Linux 系统推荐设置为小于 512 的整数。
key_buffer_size = 256M
#key_buffer_size 指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。对于内存在 4GB 左右的服务器该参数可设置为 256M 或 384M。注意：该参数值设置的过大反而会是服务器整体效率降低！
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 128K
sort_buffer_size = 6M
# 查询排序时所能使用的缓冲区大小。注意：该参数对应的分配内存是每连接独占，如果有 100 个连接，那么实际分配的总共排序缓冲区大小为 100 × 6 = 600MB。所以，对于内存在 4GB 左右的服务器推荐设置为 6 -8M。
read_buffer_size = 4M
# 读查询操作所能使用的缓冲区大小。和 sort_buffer_size 一样，该参数对应的分配内存也是每连接独享。
join_buffer_size = 8M
# 联合查询操作所能使用的缓冲区大小，和 sort_buffer_size 一样，该参数对应的分配内存也是每连接独享。
myisam_sort_buffer_size = 64M
table_cache = 512
thread_cache_size = 64
query_cache_size = 64M
# 指定 MySQL 查询缓冲区的大小。可以通过在 MySQL 控制台观察，如果 Qcache_lowmem_prunes 的值非常大，则表明经常出现缓冲不够的情况；如果 Qcache_hits 的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓冲；Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多。
tmp_table_size = 256M
max_connections = 768
# 指定 MySQL 允许的最大连接进程数。如果在访问论坛时经常出现 Too Many Connections 的错误提示，则需要增大该参数值。
max_connect_errors = 10000000
wait_timeout = 10
# 指定一个请求的最大连接时间，对于 4GB 左右内存的服务器可以设置为 5 -10。
thread_concurrency = 8
# 该参数取值为服务器逻辑 CPU 数量 *2，在本例中，服务器有 2 颗物理 CPU，而每颗物理 CPU 又支持 H.T 超线程，所以实际取值为 4 *2=8
skip-networking
# 开启该选项可以彻底关闭 MySQL 的 TCP/IP 连接方式，如果 WEB 服务器是以远程连接的方式访问 MySQL 数据库服务器则不要开启该选项！否则将无法正常连接！
table_cache=1024
# 物理内存越大, 设置就越大. 默认为 2402, 调到 512-1024 最佳
innodb_additional_mem_pool_size=4M
# 默认为 2M
innodb_flush_log_at_trx_commit=1
# 设置为 0 就是等到 innodb_log_buffer_size 列队满后再统一储存, 默认为 1
innodb_log_buffer_size=2M
# 默认为 1M
innodb_thread_concurrency=8
# 你的服务器 CPU 有几个就设置为几, 建议用默认一般为 8
key_buffer_size=256M
# 默认为 218，调到 128 最佳
tmp_table_size=64M
# 默认为 16M，调到 64-256 最挂
read_buffer_size=4M
# 默认为 64K
read_rnd_buffer_size=16M
# 默认为 256K
sort_buffer_size=32M
# 默认为 256K
thread_cache_size=120
# 默认为 60
query_cache_size=32M