MySQL初始配置调优

随着 大量默认选项的改进, MySQL 5.6比以前版本需要调优的选项大为减少。 在本文中我将讲述需要优化的配置项。

  InnoDB设置

  innodb_buffer_pool_size -- 默认值为 128M. 这是最主要的优化选项,因为它指定 InnoDB 使用多少内存来加载数据和索引(data+indexes)。 针对专用MySQL服务器,建议指定为物理内存的 50-80%这个范围。 例如,拥有64GB物理内存的机器,缓存池应该设置为50GB左右。

  innodb_log_file_size -- 默认值为 48M. 有很高写入吞吐量的系统需要增加该值以允许后台检查点活动在更长的时间周期内平滑写入,得以改进性能。 将此值设置为4G以下是很安全的。 过去的实践表明,日志文件太大的缺点是增加了崩溃时所需的修复时间,但这在5.5和5.6中已得到重大改进。innodb_flush_method -- 默认值为 fdatasync. 如果使用 硬件RAID磁盘控制器, 可能需要设置为 O_DIRECT. 这在读取InnoDB缓冲池时可防止"双缓冲(double buffering)"效应,否则会在文件系统缓存与InnoDB缓存间形成2个副本(copy)。

  MySQL用户手册 和 Bug #54306 详细地说明了这一点。innodb_flush_neighbors -- 默认值为 1. 在SSD存储上 应设置为0(禁用) ,因为使用顺序IO没有任何性能收益。 在使用RAID的某些硬件上也应该禁用此设置,因为逻辑上连续的块在物理磁盘上并不能保证也是连续的。innodb_io_capacity and innodb_io_capacity_max -- 这些设置会影响InnoDB每秒在后台执行多少操作。 在 以前的一篇文章 里我描述了大多数写IO(除了写InnoDB日志)是后台操作的。 如果你深度了解硬件性能(如每秒可以执行多少次IO操作),则使用这些功能是很可取的,而不是让它闲着。 有一个很好的类比示例: 假如某次航班一张票也没有卖出去 -- 那么让稍后航班的一些人乘坐该次航班,有可能是很好的策略,以防后面遇到恶劣的天气。 即有机会就将后台操作顺便处理了,以减少同稍后可能的实时操作产生竞争。

  innodb_lru_scan_depth - 默认值为 1024. 这是mysql 5.6中引入的一个新选项。 Mark Callaghan 提供了 一些配置建议。 简单来说,如果增大了 innodb_io_capacity 值, 应该同时增加 innodb_lru_scan_depth.复制(Replication)

  以前的文章所说, 我建议大多数用户应该以稳定性为目标。 在这种情况下,你还需要启用: sync_binlog=1, sync_relay_log=1, relay-log-info-repository=TABLE and master-info-repository=TABLE.expire-logs-days -- 默认旧日志会一直保留。 我推荐设置为 1-10 天。 保存更长的时间并没有太多用处,因为从备份中恢复会快得多。server-id -- 在一个主从复制体系(replication topology )中的所有服务器都 必须设置唯一的 server-id.binlog_format=ROW -- 修改为基于行的复制。 我最近写的另一篇 基于行的复制 ,里面叙述了我真的很喜欢它的原因,因为它可以通过减少资源锁定提高性能。 此外还需要启用两个附加设置: transaction-isolation=READ-COMMITTED and innodb_autoinc_lock_mode = 2.其他配置(Misc)

  timezone=GMT 将时区设置为格林尼治时间。 越来越多的系统管理员建议将所有服务器都设置为 格林尼治时间(GMT)。 我个人非常喜欢这点,因为现在几乎所有的业务都是全球化的。 设置为你本地的时区似乎是有点武断的。character-set-server=utf8mb4 and collation-server=utf8mb4_general_ci 如之前的 文章所讲述的 ,utf8 编码对新应用来说是更好的默认选项。 您还可以设置 skip-character-set-client-handshake 以忽略应用程序想要设置的其他字符集(character-set)。sql-mode -- MySQL默认对不规范的数据很宽容,并且会静默地截断数据。 在我 之前的一篇文章中, 我提到新应用程序最好设置为: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,

  skip-name-resolve -- 禁用反向域名解析。 DNS解析在某些系统上可能有点慢/不稳定,所以如果不需要基于主机名的授权,我建议避免这种解析。max_connect_errors -- Todd Farmer 写道 :"[这个功能]提供了没有实际意义的暴力访问攻击保护". 事实上当设置skip-name-resolve 时, max_connect_errors 甚至不起作用(见上一段所述)。 防火墙是更合适的解决方案,通常我将3306端口屏蔽,不管是公网的还是内网的端口,只有特定的应用程序可以访问和连接到MySQL.

  max_connect_errors=100000, 这样我可以避免任何"双重配置",保证它不会碍事。

  max-connections --默认值是151. 我看到很多用户将他设置得比较大,大多在 300 ~ 1000之间。 通常不可避免地这个值会被设置得更大,但让我有点紧张的是, 16核的机器在IO阻塞的情况下也只有大约 2x~10x 的连接执行能力。

  从5.5开始的另一种解决方案(在MySQL社区版和企业版之间有一些差异) 是使用 线程池插件。

  总结(Conclusion)

  可靠稳定(durable)。

  则配置可能如下所示:

  # InnoDB settings

  innodb_buffer_pool_size=50G

  innodb_log_file_size=2G

  innodb_flush_method=O_DIRECT

  innodb_io_capacity=2000

  innodb_io_capacity_max=6000

  innodb_lru_scan_depth=2000

  # Binary log/replication

  log-bin

  sync_binlog=1

  sync_relay_log=1

  relay-log-info-repository=TABLE

  master-info-repository=TABLE

  expire_logs_days=10

  binlog_format=ROW

  transaction-isolation=READ-COMMITTED

  innodb_autoinc_lock_mode = 2

  # Other

  timezone=GMT

  character-set-server=utf8

  collation-server=utf8_general_ci

  sql-mode="STRICT_TRANS_TABLES,

  ERROR_FOR_DIVISION_BY_ZERO,

  NO_AUTO_CREATE_USER,

  NO_AUTO_VALUE_ON_ZERO,

  NO_ENGINE_SUBSTITUTION,

  NO_ZERO_DATE,

  NO_ZERO_IN_DATE,

  ONLY_FULL_GROUP_BY"

  skip-name_resolve

  max-connect-errors=100000

  max-connections=500

  # Unique to this machine

  server-id=123

原文发布于微信公众号 - php(phpdaily)

原文发表时间:2015-09-16

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

1 条评论
登录 后参与评论

相关文章

来自专栏北京马哥教育

思路决定出路 | 101个MySQL调试和优化技巧

MySQL是一个功能强大的开源数据库。随着越来越多的数据库驱动的应用程序,人们一直在推动MySQL发展到它的极限。这里是101条调节和优化 MySQL安装的技巧...

2438
来自专栏数据和云

Oracle 18.3 : 透过告警日志从安装初始化过程看 18c 的新改变

Oracle Database 18c 已经正式对外发布,第一个公共版本的版本号是 18.3 ,让我们从 18.3 的安装过程来一睹 18c 的改变。

510
来自专栏程序猿DD

Spring Cloud Config采用数据库存储配置内容

在之前的《Spring Cloud构建微服务架构:分布式配置中心》一文中,我们介绍的Spring Cloud Server配置中心采用了Git的方式进行配置信息...

923
来自专栏北京马哥教育

没遇到过这十个MySQL 数据库经典错误,你一定不是个好工程师

笔者在刚开始学习数据库的时候,没少走弯路。经常会遇到各种稀奇古怪的 error 信息,遇到报错会很慌张,急需一个解决问题的办法。跟无头苍蝇一样,会不加思索地把错...

3417
来自专栏张戈的专栏

MySQL在线DDL修改表结构的简单经验分享

摘 要 在线DDL修改生产环境的大表一直是运维、DBA一个很头痛的问题,本文分享一些相关经验,希望对还在头痛的同学能有所帮助,当然更希望路过的大神,如果有更靠...

2877
来自专栏杨建荣的学习笔记

Oracle 12c DG新特性Far Sync(r10笔记第67天)

Oracle的Data Guard技术再11g中有了Active Data Guard,就产生了很多的技术解决方案,比如读写分离,多活的技术支撑等。 12c 中...

3457
来自专栏数据和云

故障分析:数据库一致性关闭缓慢问题诊断

想必我们大家都知道,Shutdown immediate即一致性关闭数据库,数据库下次启动不需要做实例恢复即可open数据库。那么当数据库一致性关闭出现缓慢等状...

2835
来自专栏大数据学习笔记

Hadoop基础教程-第10章 HBase:Hadoop数据库(10.2 HBase基本概念、框架)(草稿)

第10章 HBase:Hadoop数据库 10.2 HBase基本概念、框架 本节内容,大多是从网络上汇总而来,并做了一定总结修改。 10.2.1 HBase的...

1738
来自专栏PHP技术

优化 MySQL: 3 个简单的小调整

如果你不改变 MySQL 的缺省配置,你的服务器的性能就像题图的坏在一档的法拉利一样 “虎落平阳被犬欺” … 我并不期望成为一个专家级的 DBA,但是,在我优化...

2617
来自专栏JAVA高级架构

优化 MySQL: 3 个简单的小调整

我并不期望成为一个专家级的 DBA,但是,在我优化 MySQL 时,我推崇 80/20 原则,明确说就是通过简单的调整一些配置,你可以压榨出高达 80% 的性能...

3216

扫描关注云+社区