MySQL InnoDB性能调整的一点实践

因为JavaEye网站的数据库服务器搬家的时候被托管商的工作人员狠狠摔了一下，所以硬盘整个挂掉了，我重新安装数据库服务器的时候，顺手下载了Percona patch过的MySQL5.0版本，使用MySQL自带的heavy innodb配置文件改了改，作为my.cnf启动运行。数据库服务器的物理内存有6GB，其中有4GB可以被MySQL使用，my.cnf相关配置参数如下：

memlock
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
#innodb_flush_method=fdatasync 默认设置

buffer pool越大越好，官方推荐使用物理内存的50%－80%；log_file_size也是越大越好，官方推荐log size加起来要达到buffer pool的25%－100%。使用memlock可以避免MySQL内存进入swap，这些都是默认的推荐配置了，没有什么可以质疑的地方。但是数据库服务器启动以后，运行不太正常。表现出来的现象是：

1、操作系统内存Disk Cache使用了2.7GB
2、操作系统swap空间使用了200MB左右，一直不停进行swap in/swap out
3、CPU的IO Wait偏高，平均在10%以上

这个现象看起来非常怪异和矛盾。IO Wait偏高显然是因为频繁的使用swap进行内存换页引起的，但问题是物理内存非常空闲，操作系统明明有2.7GB空闲物理内存做Disk Cache，怎么不吐出来一点，非要去用swap呢？

想来想去只有一种可能性，就是MySQL存在非常巨大的，频繁的文件读写操作，迫使操作系统不得不分配了2.7GB的Disk Cache，从而造成了物理内存的不足，被迫使用swap。而可能造成巨大文件读写操作的就是buffer pool的flush和log file的flush操作了。因此配置文件做如下修改：

memlock
innodb_buffer_pool_size = 2G
innodb_log_file_size = 64M
innodb_log_files_in_group = 2
innodb_flush_method=O_DIRECT

减少log file size和数量，使用O_DIRECT。重启以后，数据库服务器恢复正常。操作系统Disk Cache下降到900MB，Swap使用了200多MB，但是不再进行swap in/swap out操作，CPU的IO Wait下降到2-3%。

通过这次MySQL InnoDB的调优经历，发现一些和MySQL官方推荐配置不符合的疑惑之处，值得思考和探索：

1、innodb_flush_method究竟应不应该使用O_DIRECT？

所有MySQL调优的建议都说，如果硬件没有预读功能，那么使用O_DIRECT将极大降低InnoDB的性能，因为O_DIRECT跳过了操作系统的文件系统Disk Cache，让MySQL直接读写磁盘了。

但是在我的实践中来看，如果不使用O_DIRECT，操作系统被迫开辟大量的Disk Cache用于innodb的读写缓存，不但没有提高读写性能，反而造成读写性能急剧下降。而且buffer pool的数据缓存和操作系统Disk Cache缓存造成了Double buffer的浪费，显然从我这个实践来看，浪费得非常厉害。

说O_DIRECT造成MySQL直接读写磁盘造成得性能下降问题，我觉得完全是杞人忧天。因为从JavaEye的数据库监测来看，Innodb的buffer pool命中率非常高，有98%以上，真正的磁盘操作是微乎其微的。为了1%的磁盘操作能够得到Disk Cache，而浪费了98%的double buffer内存空间，无论从性能上看，还是从内存资源的消耗来看，都是非常不明智的。

2、innodb_log_file_size究竟应该大一点，还是小一点？

所有MySQL调优建议都说，innodb_log_file_size要越大越好，避免无谓的buffer pool的flush操作。

但是在我的实践中来看，innodb_log_file_size开得太大，会明显增加innodb的log写入操作，而且会造成操作系统需要更多的Disk Cache开销。

因此从我的经验来看，innodb_flush_method=O_DIRECT是必须的，而innodb_log_file_size也不宜太大。

评论

9 楼 lection.yu 2012-09-05  

JavaEye这种高并发的论坛，请问各位为何使用InnoDB，而不是MYISAM呢？对于两个引擎的选择如何考虑？

8 楼 oliver_peng 2010-08-05  

使用memlock要注意保留足够的Free memory， 否则Linux内核在没有足够内存给Cache的时候又无法Swap内存会直接将MySQL Kill掉。

7 楼 linkerlin 2010-05-19  

我们也遇到这个问题了。
分析的很透彻啊！

6 楼 myreligion 2010-05-07  

willko 写道

innodb_log_file_size开得太大，会明显增加innodb的log写入操作

====
这个比较没明白意思，持久log的时机取决于innodb_flush_log_at_trx_commit参数，和log大小无关。

对啊，log的大小太大了好像意义不大，文档上说主要和innodb_flush_log_at_trx_commit有关。如果每天备份，又不怕丢数据库，甚至可以把log关掉，也能提高不少性能哪。

对于o_direct，好像是Peter的文档上说FreeBSD适合使用，可以提高很多性能，其他操作系统不适合。

如果内容给4G，innodb_pool_size应该是4G吧？怎么配成了2G？

5 楼 kaki 2010-04-30  

都是相对的，关键要看各种操作的比例。

4 楼 oliver_peng 2009-09-10  

请问有没有统计过访问JavaEye数据库的请求中Select, Insert, Update 和 Delete所占的比例？

3 楼 willko 2009-09-03  

innodb_log_file_size开得太大，会明显增加innodb的log写入操作

====
这个比较没明白意思，持久log的时机取决于innodb_flush_log_at_trx_commit参数，和log大小无关。

2 楼 fire9 2009-09-03  

innodb_log_file_size 大当然有好处，但是一旦宕机，恢复的时间就比较长了。

1 楼 jkfzero 2009-09-03  

广告都发到这里来了，看来猎头的确是JavaEye的主要收入之一呀。