MySQL数据库服务器端核心参数详解和推荐配置之二
时间:2016-05-29 12:37 来源:linux.it.net.cn 作者:IT
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
(责任编辑：IT)
| 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 (责任编辑：IT) |