MySQL数据库服务器端核心参数详解和推荐配置之二

2
MySQL HTML C/C++ Go 14839 次浏览
MySQL手册上也有服务器端参数的解释,以及参数值的相关说明信息,现针对我们大家重点需要注意、需要修改或影响性能的服务器端参数,作其用处的 解释和如何配置参数值的推荐,此事情拖了不少时间,为方便大家帮忙纠错(注:总是断断续续写的,可能存在笔误),先以文章的方式发布到网站,后续确定没问 题,将会通过新浪微博的微盘提供PPT下载,方便学习和参考。
l  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;
l  innodb_concurrency_tickets
含义:
同一时刻,能访问InnoDB引擎数据的线程数,默认值为500,范围1-4294967295。
补充说明:当访问InnoDB引擎数据的线程数达到设置的上线,线程将会被放到队列中,等待其他线程释放ticket。
建议:
    MySQL数据库服务最大线程连接数参数max_connections,一般情况下都会设置在128-1024的范围,再结合实际业务可能的最大事务并发度,innodb_concurrency_tickets保持默认值一般情况下足够。
l  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
#至于出问题的时候,设置为何值,要视出错的原因和程度,对数据后续做的操作
l  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文件写入警告信息;
l  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秒钟;

文章出处:http://www.mysqlops.com/2011/10/26/mysql-variable-two.html

请尽量让自己的答案能够对别人有帮助

3个答案

默认排序 按投票排序