从官方解释来看,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挂掉事务依然不会丢的好处.
整理自网络