MySQL innodb优化总结

InnoDB最重要2个参数  innodb_buffer_pool_size，innodb_log_file_size

innodb_buffer_pool_size
理论上innodb_buffer_pool_size越大越好，但是如果数值太大，导致操作系统使用swap就得不偿失了。
通过SHOW ENGINE INNODB STATUS\G 查询Free buffers，只要Free buffers 有剩余就可以了。

innodb_log_file_size，这个也不是越大越好，够用就可以，同样通过show engine innodb status\G查询，参考自http://blog.chinaunix.net/uid-20785090-id-4149489.html
...........
Log sequence number 2944118284
Log flushed up to   2944118283
Last checkpoint at  2724318261
..........
2944118284-2724318261=209MB
通过以上的计算可以看出当前的log序号到最后一次的checkpoint的时候，中间距离了209MB的空间.官方文档建议
距离最好不要超过innodb_log_files_in_group*innodb_log_file_size的0.75， innodb_log_files_in_group的
default值为2.
  一般太小的logfile size的表现情况就是checkpoint比较频繁，导致刷新dirty page到磁盘的次数增加，在
刷新时会使整个系统变得很慢.所以这种情况要尽量避免.
  从例子中得知当前的innodb_log_file_sze为5M 是远远不够的.
根据公式(innodb_log_file_size*innodb_log_files_in_group(default 2))*0.75=209M,可以算出合理的
innodb_log_file_size为140MB.

其他参数
innodb_flush_method，这个用一般来说,centos 这类用EXT3/EXT4的直接用默认设置性能比较好（有个参考，反正就是及时另外两个低负载可能稍好，但在高压下也没有区别），但是有些linux系统（或者说文件系统）可能O_DSYNC会有明显提升，参考自原文如下

在一些版本的GNU/Linux和Unix上，用Unix的fsync()（InnoDB默认使用的）把文件刷新到磁盘，并且其他相似的方法是惊人的慢。如果你不满意数据库的写性能，你可以试着设置参数 innodb_flush_method 值为 O_DSYNC，虽然 O_DSYNC 在多数系统上看起来更慢。
当在Solaris 10上，为x86_64架构（AMD Opteron)使用InnoDB存储引擎，重要的是使用forcedirectio选项来安装任何为存储与InnoDB相关的文件而使用的数据系统。（默认在Solaris 10/x86_64上不使用这个文件系统安装选项 ）。使用forcedirectio 失败会导致InnoDB在这个平台上的速度和性能严重下降。

还有另外一种情况下O_DIRECT比较好，参考自http://robbin.iteye.com/blog/461382
简单来说，内存不够diskcache的系统还是用O_DIRECT

innodb_autoinc_lock_mode
5.1默认是0，5.5默认是1，直接用1，不用多想。
原因参考自http://blog.chinaunix.net/uid-9950859-id-181376.html
部分原文

MySql插入有3中模式
Simple inserts
就是通过分析insert语句可以确定插入数量的insert语句, INSERT, INSERT … VALUES(),VALUES()
Bulk inserts
就是通过分析insert语句不能确定插入数量的insert语句, INSERT … SELECT, REPLACE … SELECT, LOAD DATA
Mixed-mode inserts
下面两种，不确定是否需要分配auto_increment id
INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');
INSERT … ON DUPLICATE KEY UPDATE

一、innodb_autoinc_lock_mode = 0 (“traditional” lock mode)
这种方式就和mysql5.1.22以前一样，为了向后兼容而保留了这种模式，如同前面介绍的一样，这种方式的特点就是“表级锁定”，并发性较差
二、innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
这种方式是新版本中的默认方式，推荐使用，并发性相对较高，特点是“consecutive”，即保证同一条insert语句中新插入的auto_increment id都是连续的。
这种模式下,对应MySQL的三种插入模式,会有三种不同的插入方法(见上面mysql的3种插入方式)：
“Simple inserts”：直接通过分析语句，获得要插入的数量，然后一次性分配足够的auto_increment id，只会将整个分配的过程锁住。
“Bulk inserts”：因为不能确定插入的数量，因此使用和以前的模式相同的表级锁定。
“Mixed-mode inserts”：直接分析语句，获得最坏情况下需要插入的数量，然后一次性分配足够的auto_increment id，只会将整个分配的过程锁住。需要注意的是，这种方式下，会分配过多的id，而导致”浪费“。比如INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');会一次性的分配5个id，而不管用户是否指定了部分id；INSERT ... ON DUPLICATE KEY UPDATE一次性分配，而不管将来插入过程中是否会因为duplicate key而仅仅执行update操作。
注意：当master mysql版本<5.1.22，slave mysql版本>=5.1.22时，slave需要将innodb_autoinc_lock_mode设置为0，因为默认的innodb_autoinc_lock_mode为1，对于INSERT ... ON DUPLICATE KEY UPDATE和INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');的执行结果不同，现实环境一般会使用INSERT ... ON DUPLICATE KEY UPDATE。
三、innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)
这种模式是来一个分配一个，而不会锁表，只会锁住分配id的过程，和innodb_autoinc_lock_mode = 1的区别在于，不会预分配多个，这种方式并发性最高。但是在replication中当binlog_format为statement-based时（简称SBR statement-based replication,就是那个只记录mysql语句的binglog模式）存在问题，因为是来一个分配一个，这样当并发执行时，“Bulk inserts”在分配的时会同时向其他的INSERT分配，会出现主从不一致（从库执行结果和主库执行结果不一样），因为binlog只会记录开始的insert id。

正常业务服务器都是单条mysql语句插入的，所以2和1效果都一样，如果要导回数据库的话，肯定是先关闭binlog才去导的。服务器上binlog_format基本都是mix的，用2估计肯定会导致主从不一致，无论安全性和效率来说2都没有必要，所以肯定是1

待续