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

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏转载gongluck的CSDN博客

cocos2dx 打灰机

#include "GamePlane.h" #include "PlaneSprite.h" #include "BulletNode.h" #include...

5286
来自专栏我和未来有约会

Silverlight第三方控件专题

这里我收集整理了目前网上silverlight第三方控件的专题,若果有所遗漏请告知我一下。 名称 简介 截图 telerik 商 RadC...

3955
来自专栏java 成神之路

使用 NIO 实现 echo 服务器

4507
来自专栏Ceph对象存储方案

Luminous版本PG 分布调优

Luminous版本开始新增的balancer模块在PG分布优化方面效果非常明显,操作也非常简便,强烈推荐各位在集群上线之前进行这一操作,能够极大的提升整个集群...

3035
来自专栏pangguoming

Spring Boot集成JasperReports生成PDF文档

由于工作需要,要实现后端根据模板动态填充数据生成PDF文档,通过技术选型,使用Ireport5.6来设计模板,结合JasperReports5.6工具库来调用渲...

1.2K7
来自专栏Golang语言社区

【Golang语言社区】GO1.9 map并发安全测试

var m sync.Map //全局 func maintest() { // 第一个 YongHuomap := make(map[st...

4658
来自专栏张善友的专栏

Silverlight + Model-View-ViewModel (MVVM)

     早在2005年,John Gossman写了一篇关于Model-View-ViewModel模式的博文,这种模式被他所在的微软的项目组用来创建Expr...

2898
来自专栏张善友的专栏

Miguel de Icaza 细说 Mix 07大会上的Silverlight和DLR

Mono之父Miguel de Icaza 详细报道微软Mix 07大会上的Silverlight和DLR ,上面还谈到了Mono and Silverligh...

2667
来自专栏魂祭心

原 canvas绘制clock

4004
来自专栏杨龙飞前端

scrollto 到指定位置

2484

扫码关注云+社区