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 条评论
登录 后参与评论

相关文章

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

ADG备库批量查询失败的原因分析(r8笔记第33天)

目前线上有一套环境是10gR2的,采用了一主两备的架构。在其中一个备库上每天凌晨会开放一个窗口运行一些批量的查询,目前使用dg broker会在指定的时间把备库...

3488
来自专栏蓝天

高性能高可用的分布式唯一ID服务——mooon-uniq-id

源码位置:https://github.com/eyjian/mooon/tree/master/application/uniq_id。

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

使用shell批量生成数据整合式迁移的脚本(r8笔记第52天)

对于数据整合式迁移,基本就是小霸王的二合一,四合一,八合一这样的节奏,把几个尽可能相关业务的数据库中的数据整合到一个库里。彼此还是独立的schema,倒也是相安...

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

关于奇怪的并行进程分析(一) (r6笔记第41天)

在使用orabbix进行监控的时候,得益于使用 实时DB time监控的选项,对于几分钟内的性能抖动也能够狠容易的记录下来,而且会把这个监控的结果基本真实反应出...

2717
来自专栏运维技术迷

Redis单线程架构

redis使用了单线程架构和I/O多路复用模型来实现高性能的内存数据库服务。 引出单线程模型 开启三个redis-cli客户端同时执行命令 客户端1设置一个字...

3348
来自专栏数据和云

举一反三:跨平台版本迁移之 XTTS 方案操作指南

作者 | 罗贵林: 云和恩墨技术工程师,具有8年以上的 Oracle 数据库工作经验,曾任职于大型的国家电信、省级财政、省级公安的维护,性能调优等。精通 Ora...

993
来自专栏谭伟华)的专栏

Amazon Aurora:云时代的数据库 ( 中)

文章详尽的介绍了 Aurora 设计背后的驱动和思考,以及如何在云上实现一个同时满足高并发、高吞吐量、高稳定性、高可用、高扩展的云数据库。

2670
来自专栏IT技术精选文摘

浅析Nginx的工作模式及原理

(微信公众号:IT技术精选文摘, 微信号:ITHK01, 欢迎订阅) 同Apache http server的Perfork工作模型类似,Nginx也有mast...

2487
来自专栏技术小黑屋

Octopress 下 Rake 失败问题解决

在 Mac 机器上,使用 octopress 总是问题重重,今天遇到了这样的问题,解决了,记录一下。

411
来自专栏后端云

内存虚拟化

除了 CPU 虚拟化,另一个关键是内存虚拟化,通过内存虚拟化共享物理系统内存,动态分配给虚拟机。虚拟机的内存虚拟化很象现在的操作系统支持的虚拟内存方式,应用程序...

721

扫码关注云+社区