针对mysql innodb配置的优化

转载请注明原文出处：http://blog.csdn.net/roddick621

针对mysql innodb配置的优化

测试环境

操作系统	Ubuntu13.04
CPU	Intel(R) Core(TM)2 Duo CPU
内存	4G
硬盘	Seagate ST2000DM001-1CH164
mysql版本	5.5.32

测试工具

工具名称	tpcc-mysql
仓库数	10个(innodb数据大概为1G)
测试线程数	2
测试前预热	120秒
测试时间	600秒

测试结果分析

具体数据从下面下载excel文档。

• innodb_buffer_pool_size
  调整Innodb缓冲池的大小。根据官方文档推荐，如果是专用服务器的话，可以把大小设置为内存的50-80%。实际情况应该根据除去已用的内存来分配一个合适的大小。推荐大小为Innodb实际数据的大小。

  innodb_buffer_pool_size大小	128M	512M	1G
  每10秒执行的次数	52.8	70.23	68.86
  提升	-	33.02%	30.43%

• innodb_flush_method
  O_DIRECT：跳过了操作系统的文件系统Disk Cache，让MySQL直接读写磁盘了。如果硬件没有预读功能，那么使用O_DIRECT将极大降低InnoDB的性能。

  innodb_flush_method类型	fdatasync	O_DIRECT
  每10秒执行的次数	52.8	29.033
  提升	-	-45.01%

• innodb_log_file_size
  innodb_log_file_size设置太大，会导致崩溃恢复时间很长。如果设置太小，日志切换就更频繁，也就直接导致更多的buffer flush，如果IO不够快，Buffer写不下去，那么日志就不能切换。官方推荐日志文件的总大小加起来应该达到缓冲池的25%-100%。

  innodb_log_file_size大小	5M	256M
  每10秒执行的次数	52.8	29.033
  提升	-	8.21%

• innodb_flush_log_at_trx_commit
  改变日志写入磁盘的策略。默认的策略1是每一次事务提交或事务外的指令都需要把日志写入硬盘。策略2是不写入硬盘而是写入系统缓存，日志仍然会每秒写入到硬盘。

  innodb_flush_log_at_trx_commit类型	1	2
  每10秒执行的次数	52.8	144.73
  提升	-	174.12%

• innodb_buffer_pool_instances
  将InnoDB的buffer pool分隔为多少个区域,增加并发能力。innodb_buffer_pool_size过小的时候增大innodb_buffer_pool_instances,会导致性能很不稳定。

  innodb_buffer_pool_instances个数	1	2	4
  每10秒执行的次数	52.8	58.43	58.55
  提升	-	10.67%	10.89%

综合测试

配置如下：

变量名	变量取值
innodb_buffer_pool_size	1G
innodb_log_file_size	256M
innodb_flush_log_at_trx_commit	2
innodb_buffer_pool_instances	1

	优化前	优化后
每10秒执行事务次数	52.8	1509
提升	-	2758.52%