? ? ? ? 很久以前對innodb_support_xa存在一點(diǎn)誤解，當(dāng)初一直認(rèn)為innodb_support_xa只控制外部xa事務(wù)，內(nèi)部的xa事務(wù)是mysql內(nèi)部進(jìn)行控制，無法人為干預(yù)（這里說的內(nèi)部xa事務(wù)主要是指binlog與innodb的redo log保持一致性所采用的內(nèi)部xa事務(wù)）。直到前陣子在微博上看到有人討論mysql數(shù)據(jù)安全時(shí)才仔細(xì)去手冊上查看了關(guān)于innodb_support_xa的解釋，這幾天又與同事再次討論了這個(gè)問題，于是想著還是將其記錄下來。先看官方手冊上對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的作用是分兩類：第一，支持多實(shí)例分布式事務(wù)（外部xa事務(wù)），這個(gè)一般在分布式數(shù)據(jù)庫環(huán)境中用得較多。第二，支持內(nèi)部xa事務(wù)，說白了也就是說支持binlog與innodb redo log之間數(shù)據(jù)一致性。今天的重點(diǎn)是討論第二類內(nèi)部xa事務(wù)。

? ? ? ? 首先我們需要明白為什么需要保持binlog與redo log之間數(shù)據(jù)一致性，這里分兩個(gè)方面來解釋：

第一，保證binlog里面存在的事務(wù)一定在redo log里面存在，也就是binlog里不會比redo log多事務(wù)（可以少，因?yàn)閞edo log里面記錄的事務(wù)可能有部分沒有commit，這些事務(wù)最終可能會被rollback）。先來看這樣一個(gè)場景（后面的場景都是假設(shè)binlog開啟）：在一個(gè)AB復(fù)制環(huán)境下主庫crash，然后進(jìn)行crash recovery，此時(shí)如果binlog里面的的事務(wù)信息與redo log里面的信息不一致，那么就會出現(xiàn)主庫利用redo log進(jìn)行恢復(fù)后，然后binlog部分的內(nèi)容復(fù)制到從庫去，然后出現(xiàn)主從數(shù)據(jù)不一致狀態(tài)。所以需要保證binlog與redo log兩者事務(wù)一致性。

第二，保證binlog里面事務(wù)順序與redo log事務(wù)順序一致性。這也是很重要的一點(diǎn)，假設(shè)兩者記錄的事務(wù)順序不一致，那么會出現(xiàn)類似于主庫事務(wù)執(zhí)行的順序是ta, tb, tc,td，但是binlog里面記錄的是ta,tc, tb, td，binlog復(fù)制到從庫后導(dǎo)致主從的數(shù)據(jù)不一致。當(dāng)然也由于當(dāng)初蹩腳的設(shè)計(jì)導(dǎo)致BGC被打破，這里就不詳說了。

? ? ? ? 為了達(dá)到上面說的兩點(diǎn)，mysql是怎么來實(shí)現(xiàn)的呢？沒錯(cuò)，答案是內(nèi)部xa事務(wù)（核心是2pc）。現(xiàn)在mysql內(nèi)部一個(gè)處理流程大概是這樣：

1. prepare ，然后將redo log持久化到磁盤

2. 如果前面prepare成功，那么再繼續(xù)將事務(wù)日志持久化到binlog

3. 如果前面成功，那么在redo log里面寫上一個(gè)commit記錄

那么假如在進(jìn)行著三步時(shí)又任何一步失敗，crash recovery是怎么進(jìn)行的呢? 此時(shí)會先從redo log將最近一個(gè)檢查點(diǎn)開始的事務(wù)讀出來，然后參考binlog里面的事務(wù)進(jìn)行恢復(fù)。如果是在1 crash，那么自然整個(gè)事務(wù)都回滾；如果是在2 crash，那么也會整個(gè)事務(wù)回滾；如果是在3 crash（僅僅是commit記錄沒寫成功），那么沒有關(guān)系因?yàn)?中已經(jīng)記錄了此次事務(wù)的binlog，所以將這個(gè)進(jìn)行commit。所以總結(jié)起來就是redo log里凡是prepare成功，但commit失敗的事務(wù)都會先去binlog查找判斷其是否存在（通過XID進(jìn)行判斷，是不是經(jīng)常在binlog里面看到Xid=xxxx？這就是xa事務(wù)id），如果有則將這個(gè)事務(wù)commit，否則rollback。

? ? ? ? 在這三個(gè)步驟中因?yàn)槌志没枨竺恳徊蕉夹枰猣sync，但是如果真的每一步都需要fsync，那么sync_binlog與innodb_flush_log_at_trx_commit兩個(gè)參數(shù)的意義又在哪？這里還沒理得很清楚，希望自己以后補(bǔ)上來或是誰幫忙解答一下。

? ? ? ? 前面已經(jīng)解釋完了通過內(nèi)部xa事務(wù)來保證binlog里記錄的事務(wù)不會比redo log多(也可以間接的理解為binlog一定只記錄提交事務(wù))，這么做的原因是為了crash recovery后主從保持一致性。接下來解釋目前是怎么來保證binlog與redo log之間順序一致的。

? ? ? ? 為什么要保證binlog里事務(wù)與redo log里事務(wù)順序一致性原因前面已經(jīng)解釋過。為了保證這一點(diǎn)帶來的問題相信了解過BGC的朋友都知道----臭名昭著的prepare_commit_mutex，沒錯(cuò)就是它導(dǎo)致了正常情況下無法實(shí)現(xiàn)BGC，原理是什么？在每次進(jìn)行xa事務(wù)時(shí)，在prepare階段事務(wù)先拿到一個(gè)全局的prepare_commit_mutex， 然后執(zhí)行前面說的持久化(fsync)redo log與binlog，然后等fsync完了之后再釋放prepare_commit_mutex，這樣相當(dāng)于串行化的效果雖然保證了binlog與redo log之間順序一致性，但是卻導(dǎo)致每個(gè)事務(wù)都需要一個(gè)fsync操作，而大家都知道在一次持久化的過程中代價(jià)最大的操作就是fsync了，而想write()這些不落地的操作代價(jià)相對來說就很小。所以BGC得核心在于很多事務(wù)需要的fsync合并成一個(gè)fsync去做。

? ? ? ? ?說了這么多就只為了解釋innodb_support_xa=1的價(jià)值在哪，但是剛才也說了由于xa事務(wù)中需要多次fsync，所以開啟后會對性能有一定影響。從percona博客上看到06年他們測試時(shí)開啟后tps下降一半，但是我實(shí)際用mysql-5.5.12+sysbench-0.5+10塊SAS（raid 10）測試結(jié)果性能下面沒那么明顯。在oltp模式下tps幾乎沒差別，不過它默認(rèn)讀寫比例是4:1，后來換成純update測試，開始xa事務(wù)性能下降也僅僅是5%左右，沒有傳說中那么大的差別。所以我懷疑可能的原因有兩個(gè)：第一，現(xiàn)在的mysql性能相對于06有了較大提升；第二，我測試的機(jī)器較好(10塊SAS盤做raid10)，這樣即使開啟了xa事務(wù)，需要較多的fsync，但是由于存儲方面能抗住，所以沒有體現(xiàn)出太大的劣勢。

? ? ? ? 接下來順便談一下innodb_flush_log_at_trx_commit意義以及合理設(shè)置。innodb_flush_log_at_trx_commit有0、1、2三個(gè)值分別代表不同的使redo log落地策略。0表示每秒進(jìn)行一次flush，但是每次事務(wù)commit不進(jìn)行任何操作（每秒調(diào)用fsync使數(shù)據(jù)落地到磁盤，不過這里需要注意如果底層存儲有cache，比如raid cache，那么這時(shí)也不會真正落地，但是由于一般raid卡都帶有備用電源，所以一般都認(rèn)為此時(shí)數(shù)據(jù)是安全的）。1代表每次事務(wù)提交都會進(jìn)行flush，這是最安全的模式。2表示每秒flush，每次事務(wù)提交時(shí)不flush，而是調(diào)用write將redo log buffer里面的redo log刷到os page cache。

? ? ? ? 那現(xiàn)在來比較三種策略的優(yōu)劣勢：1由于每次事務(wù)commit都會是redo log落地所以是最安全的，但是由于fsync的次數(shù)增多導(dǎo)致性能下降比較厲害。0表示每秒flush，每次事務(wù)提交不進(jìn)行任何操作，所以mysql crash或者os crash時(shí)會丟失一秒的事務(wù)。2相對于0來說了多了每次事務(wù)commit時(shí)會有一次write操作，此時(shí)數(shù)據(jù)雖然沒有落地到磁盤但是只要沒有 os crash，即使mysql crash，那么事務(wù)是不會丟失的。2相對于0來說會稍微安全一點(diǎn)點(diǎn)。

? ? ? ? 所以關(guān)于這兩個(gè)參數(shù)，我的建議是主庫開始innodb_support_xa=1，從庫不開(因?yàn)閺膸煲话悴粫沚inlog)，數(shù)據(jù)一致性還是很重要的。而對于innodb_flush_log_at_trx_commit，除非是對數(shù)據(jù)很重要，不能丟事務(wù)，否則我建議設(shè)置成2。我看到有些公司設(shè)置成0。其實(shí)我個(gè)人認(rèn)為都設(shè)置成0了就沒有多少理由不設(shè)置成2，因?yàn)?帶來的性能損耗是每個(gè)事務(wù)一個(gè)write操作，write操作的開銷相對于fsync還是小很多的，但是這點(diǎn)開銷換來了即使mysql掛掉事務(wù)依然不會丟的好處。

淺析innodb_support_xa與innodb_flush_log_at_trx_commit