MySQL服务器端核心参数详解和优化建议整理

标签: mysql 服务器端 核心 | 发表时间:2014-09-01 21:34 | 作者:
出处:http://jackyrong.iteye.com
  原来的来自mysqlops网站的整理,摘录如下:
max_connect_errors
max_connect_errors默认值为10,也即mysqld线程没重新启动过,一台物理服务器只要连接
异常中断累计超过10次,就再也无法连接上mysqld服务,为此建议大家设置此值至少大于等于10W;
若异常中断累计超过参数设置的值,有二种解决办法,执行命令:FLUSH HOSTS;或者重新启动mysqld服务;
interactive_timeout and wait_timeout
interactive_timeout
处于交互状态连接的活动被服务器端强制关闭,而等待的时间,单位:秒;
wait_timeout
与服务器端无交互状态的连接,直到被服务器端强制关闭而等待的时间,此参数只对基于TCP/IP或基于
Socket通信协议建立的连接才有效,单位:秒;
推荐设置
interactive_timeout = 172800
wait_timeout = 172800


binlog-format
复制的模式,可供设置的值:STATEMENT、ROW、MIXED(注:5.0.*只有命令行式复制),
5.1.*版本默认设置:MIXED;
推荐配置
①只读为主的业务应用场景
transaction-isolation = read-committed
binlog-format = mixed #5.1.*版本,5.0.*只能设置为statement
①非只读为主的业务应用场景
transaction-isolation = repeatabled-read
binlog-format = mixed #5.1.*版本,5.0.*只能设置为statement


innodb_max_dirty_pages_pct
InnoDB主线程直接更新Innodb_buffer_pool_size中存在的数据,并且不实时刷回磁盘,而是等待
相关的处罚事件发生,则允许缓存空间的数据量不实时刷回磁盘的最大百分比。比例设置较小,有利于
减少mysqld服务出现问题的时候恢复时间,缺点则是需要更多的物理I/O,为此我们必须根据业务特点
和可承受范围进行一个折中,一般范围建议设置为5%~90%,像我们SNS游戏行业的写非常厉害,综合
各方面因素,推荐设置为20%;


innodb_commit_concurrency
含义:同一时刻,允许多少个线程同时提交InnoDB事务,默认值为0,范围0-1000。
0 ---允许任意数量的事务在同一时间点提交;
N>0 ---允许N个事务在同一时间点提交;
注意事项:
①mysqld提供服务时,不许把innodb_commit_concurrency 的值从0改为非0,或非0的值改为0;
②mysqld提供时,允许把innodb_commit_concurrency 的值N>0改为M,且M>0;
innodb_concurrency_tickets
含义:同一时刻,能访问InnoDB引擎数据的线程数,默认值为500,范围1-4294967295。
补充说明:当访问InnoDB引擎数据的线程数达到设置的上线,线程将会被放到队列中,等待其他线程
释放ticket。
建议:
MySQL数据库服务最大线程连接数参数max_connections,一般情况下都会设置在128-1024的范围,
再结合实际业务可能的最大事务并发度,innodb_concurrency_tickets保持默认值一般情况下足够。

innodb_fast_shutdown and innodb_force_recovery
innodb_fast_shutdown
含义:设置innodb引擎关闭的方式,默认值为:1,正常关闭的状态;
0 ---mysqld服务关闭前,先进行数据完全的清理和插入缓冲区的合并操作,若是脏数据较多或者服务器性能
等因素,会导致此过程需要数分钟或者更长时间;
1 ---正常关闭mysqld服务,针对innodb引擎不做任何其他的操作;
2 ---若是mysqld出现崩溃,立即刷事务日志到磁盘上并且冷关闭mysqld服务;没有提交的事务将会丢失,
但是再启动mysqld服务的时候会进行事务回滚恢复;
innodb_force_recovery
含义:mysqld服务出现崩溃之后,InnoDB引擎进行回滚的模式,默认值为0,可设置的值0~6;
提示:
只有在需要从错误状态的数据库进行数据备份时,才建议设置innodb_force_recovery的值大于0。
若是把此参数作为安全选项,也可以把参数的值设置大于0,防止InnoDB引擎的数据变更;
0 ---正常的关闭和启动,不会做任何强迫恢复操作;
1 ---跳过错误页,让mysqld服务继续运行。跳过错误索引记录和存储页,尝试用SELECT * INOT OUTFILE ‘../filename’ FROM tablename;方式,完成数据备份;
2 ---阻止InnoDB的主线程运行。清理操作时出现mysqld服务崩溃,则会阻止数据恢复操作;
3 ---恢复的时候,不进行事务回滚;
4 ---阻止INSERT缓冲区的合并操作。不做合并操作,为防止出现mysqld服务崩溃。不计算表的统计信息
5 ---mysqld服务启动的时候不检查回滚日志:InnoDB引擎对待每个不确定的事务就像提交的事务一样;
6 ---不做事务日志前滚恢复操作;
推荐的参数组合配置:
innodb_fast_shutdown = 1 #若是机房条件较好可设置为0(双路电源、UPS、RAID卡电池和供电系统稳定性)
innodb_force_recovery =0 #至于出问题的时候,设置为何值,要视出错的原因和程度,对数据后续做的操作




innodb_additional_mem_pool_size
含义:开辟一片内存用于缓存InnoDB引擎的数据字典信息和内部数据结构(比如:自适应HASH索引结构);
默认值:build-in版本默认值为:1M;Plugin-innodb版本默认值为:8M;
提示:若是mysqld服务上的表对象数量较多,InnoDB引擎数据量很大,且innodb_buffer_pool_size的值设置
较大,则应该适当地调整innodb_additional_mem_pool_size的值。若是出现缓存区的内存不足,则会直接向
操作系统申请内存分配,并且会向MySQL的error log文件写入警告信息;
innodb_buffer_pool_size
含义:开辟一片内存用于缓存InnoDB引擎表的数据和索引;
默认值:历史默认值为:8M,现在版本默认值为:128M;
参数最大值:受限于CPU的架构,支持32位还是支持64位,另外还受限于操作系统为32位还是64位;
提示信息:
innodb_buffer_pool_size的值设置合适,会节约访问表对象中数据的物理IO。官方手册上建议专用的数据库服
务器,可考虑设置为物理内存总量的80%,但是个人建议要看物理服务器的物理内存总量,以及考虑:
是否只使用InnoDB引擎、mysqld内部管理占用的内存、最大线程连接数和临时表等因素,官方提供的80%值
作为一个参考,举而个例子方便大家作决定,前提为物理服务器为mysqld服务专用,且只用InnoDB引擎,假设数
据量远大于物理内存:
1).内存配置:24G 则innodb_buffer_pool_size=18G
1).内存配置:32G 则innodb_buffer_pool_size=24G
出现下列哪些情况,则可以考虑减小innodb_buffer_pool_size的值:
1).出现物理内存的竞争,可能导致操作系统的分页;
2).InnoDB预分配额外的内存给缓冲区和结构管理,当分配的总内存量超过innodb_buffer_pool_size值的10%;
3).地址空间要求必须为连续的,在windows系统有一个严重问题,DLL需要加载在特定的地址空间;
4).初始化缓冲区的时间消耗,与缓冲区的大小成正比。官方提供的数据Linux X86 64位系统初始化
innodb_buffer_pool_size=10G 大概需要6秒钟





innodb_flush_log_at_trx_commit and sync_binlog
innodb_flush_log_at_trx_commit= N:
N=0 --每隔一秒,把事务日志缓存区的数据写到日志文件中,以及把日志文件的数据刷新到磁盘上;
N=1 –每个事务提交时候,把事务日志从缓存区写到日志文件中,并且刷新日志文件的数据到磁盘上;
N=2 –每事务提交的时候,把事务日志数据从缓存区写到日志文件中;每隔一秒,刷新一次日志文件,但不一定刷新到磁盘上,而是取决于操作系统的调度;
sync_binlog = N:
N>0 --每向二进制日志文件写入N条SQL或N个事务后,则把二进制日志文件的数据刷新到磁盘上;
N=0 --不主动刷新二进制日志文件的数据到磁盘上,而是由操作系统决定;
推荐配置组合:
N=1,1 ---适合数据安全性要求非常高,而且磁盘IO写能力足够支持业务,比如充值消费系统;
N=1,0 ---适合数据安全性要求高,磁盘IO写能力支持业务不富余,允许备库落后或无复制;
N=2,0或2,m(0<m<100) ---适合数据安全性有要求,允许丢失一点事务日志,复制架构的延迟也能接受;
N=0,0 ---磁盘IO写能力有限,无复制或允许复制延迟稍微长点能接受,例如:日志性登记业务




query_cache_type and query_cache_size
query_cache_type=N
N=0 ----禁用查询缓存的功能
N=1 ----启用产讯缓存的功能,缓存所有符合要求的查询结果集,除SELECT SQL_NO_CACHE..,
以及不符合查询缓存设置的结果集外
N=2 ----仅仅缓存SELECT SQL_CACHE …子句的查询结果集,除不符合查询缓存设置的结果集外
query_cache_size
查询缓存设置多大才是合理?至少需要从四个维度考虑:
①查询缓存区对DDL和DML语句的性能影响;
②查询缓存区的内部维护成本;
③查询缓存区的命中率及内存使用率等综合考虑
④业务类型

已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [mysql 服务器端 核心] 推荐:

MySQL服务器端核心参数详解和优化建议整理

- - jackyrong
  原来的来自mysqlops网站的整理,摘录如下:. max_connect_errors默认值为10,也即mysqld线程没重新启动过,一台物理服务器只要连接. 异常中断累计超过10次,就再也无法连接上mysqld服务,为此建议大家设置此值至少大于等于10W;. 若异常中断累计超过参数设置的值,有二种解决办法,执行命令:FLUSH HOSTS;或者重新启动mysqld服务;.

从Oracle到MySQL,金融核心场景在线换库落地实战 - MySQL

- -
本文根据王英杰老师在〖deeplus直播第234期〗线上分享演讲内容整理而成. (文末有获取本期PPT&回放的方式,不要错过). 大家好,我是陆金所数据库团队的负责人王英杰. 这次的分享主要集中在陆金所去O在线换库的技术特点上,之后详细给大家剖析陆金所设计的在线换库方案以及方案如何在一个庞大的金融系统里通过多个团队的紧密配合稳妥落地.

使用 node.js 进行服务器端 JavaScript 编程

- jiaosq - IBM developerWorks 中国 : 文档库
node.js 是一个可以使用 JavaScript 开发服务器端应用的平台. 它依托于 Google V8 JavaScript 引擎,并采用事件 I/O 的架构,可以用来创建高性能服务器. 本文详细介绍了 node.js 的基本知识、模块化的结构、事件驱动的机制以及常用的模块.

socketio-netty(socket.io 服务器端JAVA实现) 近期升级手记

- - BlogJava-首页技术区
针对JAVA开发者, socketio-netty是一个socket.io的服务器端选择,又是目前兼容最新0.9+ – 1.0的JAVA服务器端实现. 从 http://socket.io官网来看,最近版本升级趋于缓和,几乎是没修正一个Bug,小版本就增加一次. 已经是非常稳定的版本了,可以真正使用了.

移动客户端与服务器端安全通信方案

- - CSDN博客移动开发推荐文章
    手机移动端与服务器端安全通信一直是个问题,让人比较头疼,最近在网上查了一些资料,总结了一下得出了一个自我感觉还好的方案,分享一下,也希望得到大家的批评斧正.     CS,C客户端,S服务器端.     在客户端软件发布前,客户端保存一个公钥,服务器保存一个私钥.     C1:客户端随机生成一个对称密钥K,使用公钥加密内容(K+账户+密码).

linux 下载并安装Memcache服务器端

- - 开源软件 - ITeye博客
下载并安装Memcache服务器端. 服务器端主要是安装memcache服务器端. 下载:http://www.danga.com/memcached/dist/memcached-1.2.2.tar.gz. 另外,Memcache用到了libevent这个库用于Socket的处理,所以还需要安装libevent,libevent的最新版本是libevent-1.3.

socketio-netty : 又一款socket.io服务器端实现,兼容0.9-1.0版本~

- - BlogJava-首页技术区
socket.io是一个跨浏览器的全平台反响AJAX实现,官网(http://socket.io)定义为:the cross-browser WebSocket for realtime apps.. 个人认为这是一个跨浏览器的集大成者,支持桌面端和移动端浏览器(http://socket.io/#browser-support):.

JavaWeb实现服务器端到客户端的验证码(MD5)校验

- - ITeye博客
首先看看服务端产生验证码的代码. (很简单 A-Z, 然后是0-9). /** * 产生验证码类(MD5)加密 * @author Thunder * */ public class VerificationCode {. * @param codeLength 指定验证码的长度. * @return 随机生成的验证码.

java nio SocketChannel 服务器端与多客户端 信息交互(聊天功能)

- - BlogJava_首页
    //解码buffer  .     /*接受数据缓冲区*/  .     /*发送数据缓冲区*/  .     /*映射客户端channel */  .          *启动服务器端,配置为非阻塞,绑定端口,注册accept事件 .          *ACCEPT事件:当服务端收到客户端连接请求时,触发该事件 .

使用Pushlet将消息从服务器端推送到客户端

- - 企业架构 - ITeye博客
使用Pushlet来实现服务器端向客户端推送信息. 1.         通过配置文件来实现定时的从服务器端向客户端推送信息. 2.         通过API主动向另外一端推送信息. 在开始测试之前,有三点非常重要,需要实现讲明,否则程序将会无法正常运行:. 2.1.     JSP页面上的设定.