MySQL 参数优化对于不同的网站, 及其在线量, 访问量, 帖子数量, 网络情况, 以及机器硬件配置都有关系, 优化不可能一次性完成, 需要不断的观察以及调试, 才有可能得到最佳的效果.
1)连接请求的变量
1,max_connections
MySQL 的最大连接数, 如果服务器的并发连接请求量较大, 建议调高此值, 以增加并行连接数量, 当然这建立在机器能支撑的情况下, 因为如果连接数越多, MySQL 回味每个连接提供连接缓冲区, 就会开销越多的内存, 所以要适当调整该值, 不能盲目提高设值.
数值过小经常会出现 ERROR 1040:Too mant connetcions 错误, 可以通过 mysql>show status like 'connections'; 通配符来查看当前状态的连接数量 (试图连接到 MySQL(不管是否连接成功) 的连接数), 以定夺该值的大小.
show variadles like 'max_connections'最大连接数
show variables like 'max_used_connection'相应连接数
max_used_connection/max_connections*100%(理想值约等于 85%)
如果 max_used_connections 和 max_connections 相同, 那么就是 max_connections 值设置过低或者超过服务器的负载上限了, 低于 10% 则设置过大了.
2,back_log
MySQL 能够暂存的连接数量. 当主要 MySQL 线程在一个很短时间内得到非常多的连接请求, 他就会起作用. 如果 MySQL 的连接数据达到 max_connections 时, 新的请求将会被存在堆栈中, 以等待某一连接释放资源, 该堆栈数量即 back_log, 如果等待连接的数量超过 back_log, 将不被接受连接资源.
3,wait_timeout 和 interative_timeout
wait_timeout: 指的是 MySQL 再关闭一个非交互的连接之前所需要等待的秒数.
interative_timeout: 指的是关闭一个交互的连接之前所需要等待的秒数.
对性能的影响
wait_timeout
(1)如果设置太小, 那么连接关闭的很快, 从而使一些持久的连接不起作用
(2)如果设置太大容易造成连接打开时间过长, 在 show processlist 时, 能够看到太多的 sleep 状态的连接, 从而造成 too many connections 错误.
(3)一般希望 wait_timeuot 尽可能的低
interative_timeout 的设置将对你的 web application 没有多大的影响
2)缓冲区变量
全局缓冲
4,key_buffer_size
key_buffer_size 指定索引缓冲区的大小, 他决定索引的处理速度, 尤其是索引读的速度. 通过检查状态值 key_read_requests 和 key_reads, 可以知道 key_buffer_size 设置是否合理. 比例 key_reads/key_read_requests 应该尽可能的低, 至少是 1:100,1:1000 更好(上述状态值可以使用 show status like 'key_read%'获得)
未命中缓存的概率:
key_cache_miss_rate = key_reads/key_read_requests*100%
key_buffer_size 只对 MAISAM 表起作用.
如何调整 key_buffer_size 的值
默认的配置数时 8388608(8M), 主机有 4G 内存可以调优值为 268435456(256M)
5,query_cache_size(查询缓存简称 QC)
使用查询缓存, MySQL 将查询结果存放在缓冲区中, 今后对同样的 select 语句(区分大小写), 将直接从缓冲区中读取结果.
一个 SQL 查询如果以 select 开头, 那么 MySQL 服务器将尝试对其使用查询缓存.
注: 两个 SQL 语句, 只要相差哪怕是一个字符(例如 大小写不一样: 多一个空格等), 那么两个 SQL 将使用不同的 cache
通过 show ststus like 'Qcache%' 可以知道 query_cache_size 的设置是否合理
Qcache_free_blocks: 缓存中相邻内存块的个数. 如果该值显示过大, 则说明 Query Cache 中的内存碎片较多了.
注: 当一个表被更新后, 和他相关的 cache block 将被 free. 但是这个 block 依然可能存在队列中, 除非是在队列的尾部. 可以用 flush query cache 语句来清空 free blocks.
Qcache_free_memory:Query Cache 中目前剩余的内存大小. 通过这个参数我们可以较为准确的观察当前系统中的 Query Cache 内存大小是否足够, 是需要增多还是过多了.
Qcache_hits: 表示有多少次命中缓存. 我们主要可以通过该值来验证我们的查询能缓存的效果. 数字越大缓存效果越理想.
Qcache_inserts: 表示多少次未命中而插入, 意思是新来的 SQL 请求在缓存中未找到, 不得不执行查询处理, 执行查询处理后把结果 insert 带查询缓存中. 这样的情况次数越多, 表示查询缓存 应用到的比较少, 效果也就不理想.
Qcache_lowmen_prunes: 多少条 Query 因为内存不足而被清除出 Query Cache, 通过 Qcache_lowmem_prunes 和 Qcache_free_memory 相互结合, 能够更清楚的了解到我们系统中 Query Cache 的内存大小是否真的足够, 是否非常频繁的出现因为内存不足而有 Query 被换出. 这个数字最好是长时间来看, 如果这个数字在不断增长, 就表示可能碎片化非常严重, 或者内存很少.
Qcache_queries_in_cache: 当前 Query Cache 中 cache 的 Query 数量
Qcache_total_blocks: 当前 Query Cache 中 block 的数量
查询服务器关于 query_cache 的配置
各字段的解释:
query_cache_limit: 超出此大小的查询将不被缓存
query_cache_min_res_unit: 缓存块的最小大小, query_cache_min_res_unit 的配置是一柄双刃剑, 默认是 4KB , 设置值大对大数据查询有好处, 但是如果你查询的都是小数据查询, 就容易造成内存碎片和浪费.
query_cache_size: 查询缓存大小(注: QC 存储的单位最小是 1024byte, 所以如果你设定的一个不是 1024 的倍数的值. 这个值会被四舍五入到最接近当前值的等于 1024 的倍数的值.)
query_cache_type: 缓存类型, 决定缓存什么样子的查询, 注意这个值不能随便设置必须设置为数字, 可选值以及说明如下:
0:OFF 相当于禁用了
1:ON 将缓存所有结果, 除非你的 select 语句使用了 SQL_NO_CACHE 禁用了查询缓存
2:DENAND 则只缓存 select 语句中通过 SQL_CACHE 指定需要缓存的查询.
query_cache_wlock_invalidate: 当有其他客户端正在对 MyISAM 表进行写操作时, 如果查询在 query cache 中, 是否返回 cache 结果还是等写操作完成在读表获取结果.
查询缓存碎片率: Qcache_free_block/Qcache_total_block*100%
如果查询缓存碎片率超过 20%, 可以用 flush query cache 整理缓存碎片, 或者试试减小 query_cache_min_res_unit, 如果你的查询都是小数据量的话.
查询缓存利用率:(query_cache_size-Qcache_free_memory)/query_cache_size*100%
查询缓存利用率在 25% 以下的话说明 query_cache_size 设置过大, 可以适当减小: 查询缓存利用率在 80% 以上而且 Qcache_lowmem_prunes>50
的话说明 query_cache_size 可能有点小, 要不就是碎片太多
查询缓存命中率: Qcache_hits/(Qcache_hits+Qcache_inserts)*100%
Query Cache 的限制
a)所有子查询中的外部查询 SQL 不能被 Cache:
b)在 p'rocedure,function 以及 trigger 中的 Query 不能被 Cache
c)包含其他很多每次执行可能得到不一样的结果的函数的 Query 不能被 Cache
6,max_connect_errors: 是一个 MySQL 中与安全有关的计数器值, 他负责阻止过多尝试失败的客户端以防止暴力破解密码的情况, 当超过指定次数, MySQL 服务器将禁止 host 的连接请求, 直到 mysql 服务器重启或通过 flush hotos 命令清空此 host 的相关信息.(与性能并无太大的关系)
7,sort_buffer_size: 每个需要排序的线程分配该大小的一个缓冲区. 增加这值加速 ORDER BY 或 GROUP BY 操作
sort_buffer_size 是一个 connection 级的参数, 在每个 connection(session)第一次需要使用这个 buffer 的时候, 一次性分配设置的内存.
sort_buffer_size: 并不是越大越好, 由于是 connection 级的参数, 过大的设置 + 高并发可能会耗尽系统的内存资源. 例如: 500 个连接将会消耗 500*sort_buffer_size(2M)=1G
8,max_allowed_packet=32M
根据配置文件限制 server 接受的数据包大小.
9,join_buffer_size=2M
用于表示关联缓存的大小, 和 sort_buffer_size 一样, 该参数对应的分配内存也是每个连接独享.
10,thread_cache_size=300
服务器线程缓存, 这个值表示可以重新利用保存在缓存中的线程数量, 当断开连接时, 那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提时缓存数未达上限), 如果线程重新被请求, 那么请求将从缓存中读取, 如果缓存中是空的或者是新的请求, 这个线程将被重新请求, 那么这个线程将被重新创建, 如果有很多新的线程, 增加这个值可以改善系统性能, 通过比较 Connections 和 Threads_created 状态的变量, 可以看到这个变量的作用.
设置规则如下: 1G 内存配置为 8,2G 内存为 16. 服务器处理此客户的线程将会缓存起来以响应下一个客户而不是被销毁(前提是缓存数未到达上限)
Threads_cached: 代表当前此时此刻线程缓存中有多少空闲线程.
Threads_connected: 代表当前已建立连接的数量, 因为一个连接就需要一个线程, 所以也可以看成当前被使用的线程数.
Threads_created: 代表最近一次服务启动, 已创建线程的数量, 如果发现 Threads_created 值过大的话, 说明 MySQL 服务器一直在创建线程, 这也比较消耗资源, 可以适当增加配置文件中 thread_cache_size 值
Threads_running: 代表当前激活的 (非睡眠状态) 线程数. 并不是代表正在使用的线程数, 有时候连接已建立, 但是连接处于 sleep 状态.
3)配置 Innodb 的几个变量
11,innodb_buffer_pool_size
对于 innodb 表来说, innodb_buffer_pool_size 的作用相当于 key_buffer_size 对于 MyISAM 表的作用一样. Innodb 使用该参数指定大小的内存来缓冲数据和索引. 最大可以把该值设置成物理内存的 80%.
12,innodb_flush_log_at_trx_commit
主要控制了 innodb 将 log buffer 中的数据写入日志文件并 flush 磁盘的时间点, 取值分别为 0,1,2.
实际测试发现, 该值对插入数据的速度影响非常大, 设置为 2 时插入 10000 条记录只需要两秒, 设置为 0 时只需要一秒, 设置为 1 时, 则需要 229 秒. 因此, MySQL 手册也建议尽量将插入操作合并成一个事务, 这样可以大幅度提高速度.
13,innodb_thread_concurrency=0
此参数用来设置 innodb 线程的并发数, 默认值为 0 表示不被限制, 若要设置则与服务器的 CPU 核心数相同或是 CPU 的核心数的 2 倍.
14,innodb_log_buffer_size
此参数确定日志文件所用的内存大小, 以 M 为单位. 缓冲区更大能提高性能, 对于较大的事务, 可以增大缓存大小.
15,innodb_log_file_size=50M
此参数确定数据日志文件的大小, 以 M 为单位, 更大的设置可以提高性能.
16,innodb_log_files_in_group=3
为提高性能, MySQL 可以以循环方式将日志文件写到多个文件. 推荐设置为 3
17,read_buffer_size=1M
MySQL 读入缓冲区大小. 对表进行顺序扫描的请求将分配到一个读入缓冲区 MySQL 会为他分配一段内存缓冲区
18,read_rnd_buffer_size=16M
MySQL 的随机读 (查询操作) 缓冲区大小. 当按任意顺序读取行时(例如, 按照排序顺序), 将分配到一个随机都缓冲区. 进行排序查询时, MySQL 会首先扫描一遍该缓冲区, 以避免磁盘搜索, 提高查询速度, 如果需要排序大量数据, 可适当调高该值. 但是 MySQL 会为每个客户连接发放该缓冲空间, 所以应尽量适当设置该值, 以避免内存消耗过大.
注: 顺序读是根据索引的叶节点数据就能顺序的读取所需要的行数据. 随机读是指一般需要根据辅助索引叶节点中的主键寻找侍其巷进行数据, 而辅助索引和主键所在的数据端不同, 因此访问方式是随机的.
19,bulk_insert_buffer_size=64M
批量插入数据缓存大小, 可以有效的提高插入效率, 默认为 8M
- 20,binary log
- binlog_cache_size=2M // 为每个 session 分配的内存, 在事务过程中用来存储二进制日志的缓存, 提高记录 bin-log 的效率.
- max_binlog_cache_size=8M // 表示的是 binlog 能够使用的最大 cache 内存大小
- max_binlog_size=512M // 指定 binlog 日志文件的大小. 不能将变量设置为大于 1G 或小于 4096 字节. 默认值为 1G. 在导入大容量的 sql 文件时, 建议关闭, sql_log_bin, 否则硬盘扛不住, 而且建议定期做删除.
- expire_logs_days=7 // 定义了 mysql 清除过期日志的时间
参数汇总:
- [mysqld]
- basedir = /usr/local/mysql
- datadir = /usr/local/mysql/data
- server_id = 1
- socket = /usr/local/mysql/mysql.sock
- log-error = /usr/local/mysql/data/mysqld.err
- slow_query_log = 1
- slow_query_log_file=/usr/local/mysql/data/slow-query.log
- long_query_time = 1
- log-queries-not-using-indexes
- max_connections = 1024
- back_log = 128
- wait_timeout = 60
- interactive_timeout = 7200
- key_buffer_size = 256M
- query_cache_size = 256M
- query_cache_type = 1
- query_cache_limit = 50M
- max_connect_errors = 20
- sort_buffer_size = 2M
- max_allowed_packet = 32M
- join_buffer_size = 2M
- thread_cache_size = 200
- innodb_buffer_pool_size = 2048M
- innodb_flush_log_at_trx_commit = 1
- innodb_log_buffer_size = 32M
- innodb_log_file_size = 128M
- innodb_log_files_in_group = 3
- log-bin=/usr/local/mysql/data/mysqlbin
- binlog_cache_size = 2M
- max_binlog_cache_size = 8M
- max_binlog_size = 512M
- expire_logs_days = 7
- read_buffer_size = 1M
- read_rnd_buffer_size = 16M
- bulk_insert_buffer_size = 64M
- # Remove leading # and set to the amount of RAM for the most important data
- # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
- # innodb_buffer_pool_size = 128M
- # Remove leading # to turn on a very important data integrity option: logging
- # changes to the binary log between backups.
- # log_bin
- # These are commonly set, remove the # and set as required.
- # basedir = .....
- # datadir = .....
- # port = .....
- # server_id = .....
- # socket = .....
- # Remove leading # to set options mainly useful for reporting servers.
- # The server defaults are faster for transactions and fast SELECTs.
- # Adjust sizes as needed, experiment to find the optimal values.
- # join_buffer_size = 128M
- # sort_buffer_size = 2M
- # read_rnd_buffer_size = 2M
- sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
来源: http://www.bubuko.com/infodetail-2690917.html