innodb 保证数据一致性

数据库为了获得最高性能，会将数据或日志写入缓冲，而如果数据库或操作系统崩掉，会导致数据和日志文件不一致

事物性较强的系统，为了获得完全一致性，要设置以下三个参数

sync-binlog=1   (二进制日志文件参数)

 

innodb-support-xa=1 （事物参数）

 

innodb_flush_log_at_trx_commit=1 （重做日志文件参数）

 

以下是这三个参数的详细解释

 

1、sync-binlog 二进制文件同步方式的参数，sync-binlog=N 表示每写N个缓冲就同步到磁盘。设成1，就是每

 

写一次就同步到磁盘。也就是和磁盘同步。设为1，还会有一种情况发生不一致。在事物commit之前，会将二

 

进制写入磁盘。这时事物还没有提交，发生了宕机，数据库重启，commit没有提交，会被回滚。而此时，二

 

进制文件内已经有了这个还没有提交的事物，而二进制文件不会回滚。导致不一致。这个问题可以通过设置

 

innodb-support-xa=1来解决

 

2、从官方解释来看，innodb_support_xa的作用是分两类：第一，支持多实例分布式事务（外部xa事务），这个一般在分布式数据库环境中用得较多。第二，支持内部xa事务，说白了也就是说支持binlog与innodb redo log之间数据一致性。今天的重点是讨论第二类内部xa事务。

        首先我们需要明白为什么需要保持binlog与redo log之间数据一致性，这里分两个方面来解释：

第一，保证binlog里面存在的事务一定在redo log里面存在，也就是binlog里不会比redo log多事务（可以少，因为redo log里面记录的事务可能有部分没有commit，这些事务最终可能会被rollback）。先来看这样一个场景（后面的场景都是假设binlog开启）：在一个AB复制环境下主库crash，然后进行crash recovery，此时如果binlog里面的的事务信息与redo log里面的信息不一致，那么就会出现主库利用redo log进行恢复后，然后binlog部分的内容复制到从库去，然后出现主从数据不一致状态。所以需要保证binlog与redo log两者事务一致性。

第二，保证binlog里面事务顺序与redo log事务顺序一致性。这也是很重要的一点，假设两者记录的事务顺序不一致，那么会出现类似于主库事务执行的顺序是ta, tb, tc,td，但是binlog里面记录的是ta,tc, tb, td，binlog复制到从库后导致主从的数据不一致。当然也由于当初蹩脚的设计导致BGC被打破，这里就不详说了。

        为了达到上面说的两点，mysql是怎么来实现的呢？没错，答案是内部xa事务（核心是2pc）。现在mysql内部一个处理流程大概是这样：

1. prepare ，然后将redo log持久化到磁盘

2. 如果前面prepare成功，那么再继续将事务日志持久化到binlog

3. 如果前面成功，那么在redo log里面写上一个commit记录

那么假如在进行着三步时又任何一步失败，crash recovery是怎么进行的呢? 此时会先从redo log将最近一个检查点开始的事务读出来，然后参考binlog里面的事务进行恢复。如果是在1 crash，那么自然整个事务都回滚；如果是在2 crash，那么也会整个事务回滚；如果是在3 crash（仅仅是commit记录没写成功），那么没有关系因为2中已经记录了此次事务的binlog，所以将这个进行commit。所以总结起来就是redo log里凡是prepare成功，但commit失败的事务都会先去binlog查找判断其是否存在（通过XID进行判断，是不是经常在binlog里面看到Xid=xxxx？这就是xa事务id），如果有则将这个事务commit，否则rollback。

        在这三个步骤中因为持久化需求每一步都需要fsync，但是如果真的每一步都需要fsync，那么sync_binlog与innodb_flush_log_at_trx_commit两个参数的意义又在哪？这里还没理得很清楚，希望自己以后补上来或是谁帮忙解答一下。

        前面已经解释完了通过内部xa事务来保证binlog里记录的事务不会比redo log多(也可以间接的理解为binlog一定只记录提交事务)，这么做的原因是为了crash recovery后主从保持一致性。接下来解释目前是怎么来保证binlog与redo log之间顺序一致的。

        为什么要保证binlog里事务与redo log里事务顺序一致性原因前面已经解释过。为了保证这一点带来的问题相信了解过BGC的朋友都知道----臭名昭著的prepare_commit_mutex，没错就是它导致了正常情况下无法实现BGC，原理是什么？在每次进行xa事务时，在prepare阶段事务先拿到一个全局的prepare_commit_mutex， 然后执行前面说的持久化(fsync)redo log与binlog，然后等fsync完了之后再释放prepare_commit_mutex，这样相当于串行化的效果虽然保证了binlog与redo log之间顺序一致性，但是却导致每个事务都需要一个fsync操作，而大家都知道在一次持久化的过程中代价最大的操作就是fsync了，而想write()这些不落地的操作代价相对来说就很小。所以BGC得核心在于很多事务需要的fsync合并成一个fsync去做。

         说了这么多就只为了解释innodb_support_xa=1的价值在哪，但是刚才也说了由于xa事务中需要多次fsync，所以开启后会对性能有一定影响。从percona博客上看到06年他们测试时开启后tps下降一半，但是我实际用mysql-5.5.12+sysbench-0.5+10块SAS（raid 10）测试结果性能下面没那么明显。在oltp模式下tps几乎没差别，不过它默认读写比例是4:1，后来换成纯update测试，开始xa事务性能下降也仅仅是5%左右，没有传说中那么大的差别。所以我怀疑可能的原因有两个：第一，现在的mysql性能相对于06有了较大提升；第二，我测试的机器较好(10块SAS盘做raid10)，这样即使开启了xa事务，需要较多的fsync，但是由于存储方面能抗住，所以没有体现出太大的劣势。

        接下来顺便谈一下innodb_flush_log_at_trx_commit意义以及合理设置。innodb_flush_log_at_trx_commit有0、1、2三个值分别代表不同的使redo log落地策略。0表示每秒进行一次flush，但是每次事务commit不进行任何操作（每秒调用fsync使数据落地到磁盘，不过这里需要注意如果底层存储有cache，比如raid cache，那么这时也不会真正落地，但是由于一般raid卡都带有备用电源，所以一般都认为此时数据是安全的）。1代表每次事务提交都会进行flush，这是最安全的模式。2表示每秒flush，每次事务提交时不flush，而是调用write将redo log buffer里面的redo log刷到os page cache。

        那现在来比较三种策略的优劣势：1由于每次事务commit都会是redo log落地所以是最安全的，但是由于fsync的次数增多导致性能下降比较厉害。0表示每秒flush，每次事务提交不进行任何操作，所以mysql crash或者os crash时会丢失一秒的事务。2相对于0来说了多了每次事务commit时会有一次write操作，此时数据虽然没有落地到磁盘但是只要没有 os crash，即使mysql crash，那么事务是不会丢失的。2相对于0来说会稍微安全一点点。

        所以关于这两个参数，我的建议是主库开始innodb_support_xa=1，从库不开(因为从库一般不会记binlog)，数据一致性还是很重要的。而对于innodb_flush_log_at_trx_commit，除非是对数据很重要，不能丢事务，否则我建议设置成2。我看到有些公司设置成0。其实我个人认为都设置成0了就没有多少理由不设置成2，因为2带来的性能损耗是每个事务一个write操作，write操作的开销相对于fsync还是小很多的，但是这点开销换来了即使mysql挂掉事务依然不会丢的好处。

3、innodb_flush_log_at_trx_commit 默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘.设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。 

 

参考文献

1《mysql 技术内幕 InnoDB 存储引擎》

2 http://www.360doc.com/content/14/1019/00/12904276_418042975.shtml

3 http://www.cnblogs.com/whiteyun/archive/2011/12/01/2270132.html