MySQL InnoDB优化（经典）

_N_了吧唧的_

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


C代码  

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

　　

　　
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 >　　

　　

　　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操作了。因此配置文件做如下修改：
　　

　　


C代码  

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

　　

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

　　

　　


　　
减少log file>　　

　　
通过这次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也不宜太大。
　　转自：http://robbin.iteye.com/blog/461382