浅析innodb_support_xa与innodb_flush_log_at_trx_commit_MySQL

浅析innodb_support_xa与innodb_flush_log_at_trx_commit

很久以前对innodb_support_xa存在一点误解，当初一直认为innodb_support_xa只控制外部xa事务，内部的xa事务是mysql内部进行控制，无法人为干预（这里说的内部xa事务主要是指binlog与innodb的redo log保持一致性所采用的内部xa事务）。直到前阵子在微博上看到有人讨论mysql数据安全时才仔细去手册上查看了关于innodb_support_xa的解释，这几天又与同事再次讨论了这个问题，于是想着还是将其记录下来。先看官方手册上对innodb_support_xa的解释：

“EnablesInnoDBsupport for two-phase commit in XA transactions, causing an extra disk flush for transaction preparation. This set-ting is the default. The XA mechanism is used internally and is essential for any server that has its binary log turned on and is accepting changes to its data from more than one thread. If you turn it off, transactions can be written to the binary log in a different order from the one in which the live database is committing them. This can produce different data when the binary log is replayed in disaster recovery or on a replication slave. Do not turn it off on a replication master server unless you have an unusual setup where only one thread is able to change data.”

从官方解释来看，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挂掉事务依然不会丢的好处。