在使用和運維 MySQL 的過程中你一定碰到過下面這種奇怪的慢 SQL。

• • 平時執行很快的 INSERT 語句，竟然執行了1.3s，並且慢 SQL 記錄裏也沒有看到長時間的鎖等待。

• • 多語句事務的所有語句都已經執行完了，但是 COMMIT 語句竟然執行了1.3s。

當這種情況出現時，最有可能的就是有大事務在提交。以下是一個模擬測試的結果，我們用 Sysbench 來模擬正常的業務，然後在後臺每 5 秒執行一個大的 UPDATE，可以看到大的 UPDATE 會嚴重的影響業務的性能。

根因分析

上圖展示了兩個事務的執行過程：

• • 事務執行分爲兩個階段，執行階段和提交階段。

• • 事務執行階段，當語句更新數據時，會生成 Binlog Events。這些 Binlog events 會被存儲到 Binlog Cache 中，Binlog Cache 有兩部分，一部分是內存的 Buffer，一部分是一個臨時文件，當 Buffer 寫滿後，就會將 Binlog Events 寫入到臨時文件中。

• • 當事務提交時，會將 Binlog Cache 中的 Binlog Events 全部拷貝到 Binlog 文件中。

• • 將Binlog Events寫入Binlog文件的過程必須要串行執行，只有一個事務寫完了，另外一個事務才能執行。因此當Trx_n在寫 Binlog 文件時，Trx_m就必須等待。

• • 圖中Trx_n是一個大事務，產生了大量的 Binlog Events。拷貝Binlog Events到Binlog 文件所需要的時間是和事務產生的Binlog Events大小線性相關的，Binlog Events越大，拷貝的時間就越長。

• • Trx_m是一個小事務，雖然執行階段很快就完成了，但是在提交時，遇到了大事務Trx_n在提交，因此必須要等待Trx_n拷貝完 Binlog Events 才能繼續。Trx_m在提交階段花了大量的時間在等待Trx_n寫 Binlog 文件，這就是小事務變慢的原因。

問題的嚴重性

從前面我們的模擬測試我們可以看到，大事務的提交對業務的穩定性是有非常大的影響的。實際的使用場景中可能要嚴重的多，並且也很普遍。

• • GB 量級的事務會造成實例長時間的不可寫。由於存儲的 IO 帶寬是一定的，大事務寫 binlog 的時間就取決於事務的大小。在運維的過程中，我們見到的最大的事務產生了104GB的 Binlog Events。

• • GB 量級的事務會造成 IO 吞吐上升變慢, 甚至 IO 打滿，這也會導致查詢變慢。

• • 幾百 MB 的事務雖然不會造成長時間的影響，但仍有會讓業務 DML 慢上幾百毫秒。這種程度的慢，對於延遲敏感型的業務來說，可能也是不能接受的。

• • 除此之外，以上的幾種情況都可能引起活躍連接數的上升。如果活躍連接不能及時消化，導致 CPU 打高，可能造成惡性循環，最終雪崩，產生更大的問題。

大事務寫 Binlog 優化

我們在 AliSQL 上對大事務寫 Binlog 的過程做了優化，徹底消除了大事務提交對穩定性的影響。RDS-5.7，RDS-8.0 都已經默認的開啓了這個優化。去年我們將 AliSQL 的這個優化捐贈給了 MariaDB，這個功能已經在 MariaDB-11.7[1] 上發佈。

優化方案

下面我來介紹一下 MariaDB-11.7 上的實現方案，MySQL 和 MariaDB 代碼上雖然差別已經比較大了，但是根本的邏輯還是一樣的，所以實現方案也是一樣的。

這個優化方案說起來是比較簡單和清晰的：既然 Binlog Cache 已經將 Binlog Events 寫到了文件裏，那我們就直接把這個文件直接轉成(rename)一個Binlog文件。這樣就不需要拷貝 Binlog Events，沒有額外的 IO 產生。並且 Rename 操作的時間是恆定的和 Binlog Cache 的大小無關，可以徹底解決大事務造成的問題。下面我們來看一下實現邏輯。

#binlog_cache_files 目錄

Binlog Cache 裏的文件是一個系統臨時文件，不能直接轉成一個普通文件。因此我們在 binlog 所在的目錄創建了一個目錄#binlog_cache_files，Binlog Cache 創建的文件從系統臨時文件變成了普通的文件，放在這個目錄裏。

$ls var/mysqld.1/data/#binlog_cache_files                              
ML_140413554102520

頭部保留空間

Binlog cache 的文件裏只包含事務的 Binlog Events，如果要轉成 Binlog 文件，則需要保留一定的空間用來寫 Binlog 文件頭的 Binlog Events，比如 Format_description_event 等。

預留的空間是按 4KB 對齊的，因此至少會預留 4KB。對於大多數情況來說，4KB 的空間已經足夠。但是在一些場景下 Gtid_list_log_event(類似於 MySQL 的 Previous_gtid_event 用來記錄這個 Binlog 之前生成的 Gtid 集合) 可能會非常大。爲了避免在這種情況下這個功能無法使用，在生成新的 Binlog 文件時會根據 Binlog 文件頭部 Events 實際佔用的空間大小來調整保留的空間。當下一個事務開始時，會調整 Binlog Cache 文件的保留空間。

Binlog 頭部的 Binlog Events 通常佔用不到 4KB 的空間，因此在寫完頭部的 Binlog Events 後，可能會剩餘一些空間。如何處理這些剩餘空間呢？得益於 MariaDB Gtid_log_event 可以在末尾填充 0 的機制，這些剩餘空間會被填充到對應的 Gtid_log_event 中。在將 Binlog Cache 文件轉成 Binlog 文件後，其結構如下所示：

Rename 過程

Rename 的主要過程如下：

• • 持久化 Binlog Cache 文件，此時還沒有進入 Rename 過程，不會阻塞其他事務提交。

• • 執行 Rotate 的過程，關閉原來的 Binlog 文件，產生一個新的 Binlog 文件。

• • 將新文件的 Header 的內容拷貝到 Binlog Cache 頭部。

• • 生成 Gtid_log_event.

• • 刪除生成的 Binlog 文件，將 Binlog Cache 文件 Rename 成新的 Binlog 文件。

優化效果

我們仍然用 sysbench 模擬業務執行，然後在後臺每 5 秒執行一個大的 UPDATE，這個 UPDATE 會產生 512MB 的 Binlog Events。測試結果如下：

在有大事務提交優化的情況下, sysbench 的 TPS 已經比較平穩，不會出現劇烈的抖動。看起來每 5 秒仍然會有一個小幅的抖動，這個抖動是因爲執行大的 UPDATE 本身要佔用一定的 CPU，而不是事務提交導致的。

此外我們也模擬了不同大小的事務，對業務 SQL 造成的延遲情況。結果如下圖所示：

• • 在沒有大事務提交優化的情況下，當大事務超過 64MB 後 sysbench 的最大延遲開始明顯增加，並且隨着事務變大，迅速增加。

• • 當開啓了大事務提交優化後, 無論事務多大 sysbench 的最大延遲始終保持穩定，保持正常業務延遲水平。當事務達到 1024GB 時，因爲多了一次 Binlog Rotate，所以我們看到延遲略有增加。

總結

在 MySQL Binlog 複製架構中，大事務是一個比較典型的問題導火索，會導致穩定性、複製延遲等問題。通過將 Binlog Cache 的臨時文件直接轉成 Binlog 文件的方法，可以避免對於 Binlog Events 的拷貝，消除額外的 IO，讓大事務的提交始終保持高效和穩定。因此徹底解決了大事務提交導致的各種穩定性問題。

引用鏈接

[1] MariaDB-11.7: https://mariadb.com/resources/blog/binlog-commit-optimization-for-large-transaction/

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/ET5IrNoqHoAC-BaHPYoFGw