MySQL版《一周一议》之如何优化mysql配置参数（积分已转账-2012-12-19）

chinafenghao

20积分已转账，请注意查收！

2012年12月11日
感谢大家对上周话题的关注。
现在隆重宣布上周获得《Cassandra实战指南》的用户是：huzi1986
另外cenalulu和xike2002各获得200积分奖励。
其他参与讨论的用户到将至少获得20积分,积分是需要管理员手工加，加上后会发站内信的。

本周的话题也是偏技术方面的。
对于所有类型的数据库优化来说，优化的地方无非是,硬件,系统,数据库软件配置,数据库架构,程序架构,sql语句这些方面下工夫。但是这些优化方案中成本最低，效果最好的是数据库软件配置参数优化,也就是通过调整mysql的参数来达到数据库优化的目的,不需要任何应用层的介入，也不会增加任何的硬件成本。所以mysql配置参数优化是刀中之王-------不是屠龙宝刀,是菜刀,居家\旅行必备工具。

今天我们的话题就是mysql 配置参数优化方案。

1、你认为最重要的,对mysql性能影响最大的几个配置参数。

2、针对OLTP环境+SSD硬盘,你的mysql优化参数。

3、贴出你的配置文件,附带业务环境和硬件配置,重点字段的备注。
例如:
业务环境:3D MMORPG
硬件环境：DELL R610  Intel(R) Xeon(R) CPU E5620  @ 2.40GHz *2  32GRAM
配置文件:


活动奖励:
1、每周会评选一位最活跃的用户，有一本mysql相关的书籍送出,本周送出的书籍是《PHP与MySQL权威指南》。

2、由于是话题讨论，所以每位参加者都能获得适当的积分奖励。最低20分，最高不限,^_^想要赚分的朋友也可以来凑热闹哟。

chinafenghao

我个人觉得对数据库性能影响最大的参数有以下几个:
innodb_buffer_pool_size = 4G
innodb_flush_log_at_trx_commit = 2
sync_binlog=1
query_cache_size = 512M


大家继续补充吧。

hunter_search

chinafenghao 发表于 2012-12-11 16:58
我个人觉得对数据库性能影响最大的参数有以下几个:
innodb_buffer_pool_size = 4G
innodb_flush_log_at_t ...

随便聊天一下

innodb_flush_log_at_trx_commit  0 ,1, 2 这个就无需过度阐释，论坛讨论N次了
innodb_buffer_pool_size  这个虽说越大越好，但注意大内存下numa问题, 避免出现内存还有，但出现swap
innodb_flush_method  fsync【默认】 , o_direct , o_dsync,  这些参数设置后，会对事务日志IO, 数据刷盘的IO,产生影响,这些参数都跟linux kernel的实现有关系, 实际生产环境中，o_direct 跟默认模式的性能相差没有想象中那么大.

另外将ibdata 和 ib_logfile 分别放到不同的磁盘，进一步提升 IO的性能

另外跟IO相关的还有
innodb_io_capacity   默认是200页
innodb_log_file_size  这个太小需要计算合适大小, 如果是空间不够【达到某个百分比】，到checkpoint时候，很可能阻塞,严重影响性能
另外sync_binlog = 1  也就是说，每向二进制日志文件写入1条SQL或1个事务就需要flush一次磁盘, 这个虽然安全，性能应该不高, 生产环境中，我们是默认的0，
  楼下继续
   

lxw2016

ssd  硬盘没有在生产中用，这个不好说。
我们公司在生产中除了配置好LS参数外，硬盘用的是raid10，以便提高IO性能，另外如果数据量较大，一定要做读写分离。

seesea2517

我们主要用的是 innodb，这个参数是首要配置的：innodb_flush_log_at_trx_commit
然后我们必然要配置的是：skip-name-resolve

3楼讲的很好了，都没什么特别要说的了。
推荐大家一个工具吧：mysqltuner。

chinafenghao

@lxw2016@hunter_search
SSD的话
nnodb_io_capacity 这个参数是必须调整。

chinafenghao

https://tools.percona.com/ 给大家分享一个percona的工具吧，可以帮助你生成一个mysql配置文件。

chinafenghao

@seesea2517
这个工具的结果挺直观的。很棒

action08

seesea2517 发表于 2012-12-12 10:18
我们主要用的是 innodb，这个参数是首要配置的：innodb_flush_log_at_trx_commit
然后我们必然要配置的是： ...

这个工具可靠度有限，就跟相亲算命软件一样，还要注册{:3_197:}

seesea2517

回复 9# action08


    不会吧，这个工具就是一个脚本，随处可以下载的。