MySQL 壓縮二進位制日誌

原文：

配置 限制 監控 不同負載示例 舉例-壓縮級別 總結

在繁忙的伺服器上，二進位制日誌最終可能成為磁碟空間使用量最大的來源之一。這意味著更高的I/O，更大的備份（您備份了您的二進位制日誌，是嗎？），從節點獲取日誌時可能會有更多的網路流量，等等。通常，二進位制日誌壓縮效果很好，所以人們一直希望有一個功能可以在MySQL使用二進位制日誌時對其進行壓縮。從MySQL8.0.20開始，現在可以了。我將在這篇博文中看看這個新功能。

配置

二進位制日誌壓縮功能由2個引數控制，一個用於啟用壓縮，一個用於指定壓縮級別。總結如下表所示。

引數名	預設值	允許設定範圍
binlog_transaction_compression	OFF	OFF/ON
binlog_transaction_compression_level_zstd	3	1-22

這兩個引數名稱很容易解釋。 binlog_transaction_compression指定是否啟用壓縮， binlog_transaction_compression_level_zstd指定壓縮級別。在較高的級別上，以增加CPU使用的代價實現更好的壓縮（原則上來說——參見稍後博文中的測試）。

這兩個引數都可以在全域性和會話的範圍內動態設定。但是，不允許在事務執行過程中更改會話值。如果您這樣做，您會得到以下報錯：

mysql> SET SESSION binlog_transaction_compression = ON;
ERROR: 1766 (HY000): The system variable binlog_transaction_compression cannot be set when there is an ongoing transaction.

儘管配置二進位制日誌壓縮很容易，但是您應該知道有一些限制。

限制

限制簡單歸結為只壓縮事務行事件。不壓縮基於語句的事件、GTID資訊、迴圈事件、非事務表的基於行事件等。另外，如果您有一個事務中包含非事務性更改，那麼這兩個更改都不會被壓縮。

如果您使用二進位制日誌的所有預設值，並使用InnoDB儲存引擎（預設），壓縮將起作用。有關限制的完整列表，請參閱手冊中的二進位制日誌事務壓縮。

這也意味著每個事務都是獨立壓縮的。請參閱部落格後面的示例理解其中含義。

正如我經常說的，監控是瞭解系統的關鍵。怎樣從監控中檢視二進位制日誌壓縮功能？

監控

有二個方法可以監控二進位制日誌壓縮功能的效能。一個Performance Schema中的壓縮統計表和新的階段事件。

Performance Schema中的 binary_log_transaction_compression_stats表包含了從上次MySQL重啟（或者上次表被截斷）的壓縮的統計資訊。對二進位制日誌，這張表有兩行，一行記錄壓縮事件，一行記錄未壓縮事件。從節點對中繼日誌也類似地記錄兩行資料。記錄二進位制日誌的示例如下：

mysql> SELECT * FROM binary_log_transaction_compression_stats\G
*************************** 1. row ***************************
                            LOG_TYPE: BINARY
                    COMPRESSION_TYPE: ZSTD
                 TRANSACTION_COUNTER: 15321
            COMPRESSED_BYTES_COUNTER: 102796461
          UNCOMPRESSED_BYTES_COUNTER: 252705572
              COMPRESSION_PERCENTAGE: 59
                FIRST_TRANSACTION_ID: 74470a0c-8ea4-11ea-966e-080027effed8:30730
  FIRST_TRANSACTION_COMPRESSED_BYTES: 313
FIRST_TRANSACTION_UNCOMPRESSED_BYTES: 363
         FIRST_TRANSACTION_TIMESTAMP: 2020-05-07 19:26:37.744437
                 LAST_TRANSACTION_ID: 74470a0c-8ea4-11ea-966e-080027effed8:46058
   LAST_TRANSACTION_COMPRESSED_BYTES: 712
 LAST_TRANSACTION_UNCOMPRESSED_BYTES: 1627
          LAST_TRANSACTION_TIMESTAMP: 2020-05-07 19:38:14.149782
*************************** 2. row ***************************
                            LOG_TYPE: BINARY
                    COMPRESSION_TYPE: NONE
                 TRANSACTION_COUNTER: 20
            COMPRESSED_BYTES_COUNTER: 5351
          UNCOMPRESSED_BYTES_COUNTER: 5351
              COMPRESSION_PERCENTAGE: 0
                FIRST_TRANSACTION_ID: 74470a0c-8ea4-11ea-966e-080027effed8:30718
  FIRST_TRANSACTION_COMPRESSED_BYTES: 116
FIRST_TRANSACTION_UNCOMPRESSED_BYTES: 116
         FIRST_TRANSACTION_TIMESTAMP: 2020-05-07 19:26:37.508155
                 LAST_TRANSACTION_ID: 74470a0c-8ea4-11ea-966e-080027effed8:31058
   LAST_TRANSACTION_COMPRESSED_BYTES: 116
 LAST_TRANSACTION_UNCOMPRESSED_BYTES: 116
          LAST_TRANSACTION_TIMESTAMP: 2020-05-07 19:30:30.840767
2 rows in set (0.0026 sec)
mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000142 |       240 | No        |
| binlog.000143 |      4933 | No        |
| binlog.000144 |  28238118 | No        |
| binlog.000145 |  24667167 | No        |
| binlog.000146 |  39221771 | No        |
| binlog.000147 |  11944631 | No        |
| binlog.000148 |       196 | No        |
+---------------+-----------+-----------+
7 rows in set (0.0005 sec)

該示例啟用了GTID，除了

這表明有15341個事務（74470a0c-8ea4-11ea-966e-080027effed8:30718-46058），其中15321個已被壓縮，平均壓縮比為59%（將252,705,572位元組壓縮至102,796,461位元組）。還有第一個和最近一次被壓縮的事務的統計資訊。類似的，有20個“事務”不能被壓縮。

在知道壓縮率的同時，您還需要知道執行壓縮和解壓縮的開銷。可以使用Performance Schema中的兩個階段事件進行監控：

• stage/sql/Compressing transaction changes.

• stage/sql/Decompressing transaction changes.
  （句號是名字的一部分。）

預設情況下，這兩個事件的instrument均未啟用，要收集資訊，您還需要啟用 events_stages_current的consumer。例如，在配置檔案中啟用：

[mysqld]
performance-schema-instrument = "stage/sql/%Compressing transaction changes.=ON"
performance-schema-consumer-events-stages-current = ON

啟用階段檢測會產生一些開銷。如果您考慮在生產系統上啟用這些功能，您需要先測試影響。

您可以將其與 wait/io/file/sql/binlog事件（預設啟用）進行比較，後者是執行I/O所花費時間。例如：

mysql> SELECT EVENT_NAME, COUNT_STAR,
              FORMAT_PICO_TIME(SUM_TIMER_WAIT) AS total_latency,
              FORMAT_PICO_TIME(MIN_TIMER_WAIT) AS min_latency,
              FORMAT_PICO_TIME(AVG_TIMER_WAIT) AS avg_latency,
              FORMAT_PICO_TIME(MAX_TIMER_WAIT) AS max_latency
         FROM performance_schema.events_stages_summary_global_by_event_name
        WHERE EVENT_NAME LIKE 'stage/sql/%transaction changes.'\G
*************************** 1. row ***************************
   EVENT_NAME: stage/sql/Compressing transaction changes.
   COUNT_STAR: 15321
total_latency: 6.10 s
  min_latency: 22.00 ns
  avg_latency: 397.96 us
  max_latency: 982.12 ms
*************************** 2. row ***************************
   EVENT_NAME: stage/sql/Decompressing transaction changes.
   COUNT_STAR: 0
total_latency:   0 ps
  min_latency:   0 ps
  avg_latency:   0 ps
  max_latency:   0 ps
2 rows in set (0.0008 sec)
mysql> SELECT *
         FROM sys.io_global_by_wait_by_latency
        WHERE event_name = 'sql/binlog'\G
*************************** 1. row ***************************
   event_name: sql/binlog
        total: 27537
total_latency: 4.83 min
  avg_latency: 10.51 ms
  max_latency: 723.92 ms
 read_latency: 138.25 us
write_latency: 290.69 ms
 misc_latency: 4.82 min
   count_read: 37
   total_read: 1002 bytes
     avg_read:   27 bytes
  count_write: 16489
total_written: 99.26 MiB
  avg_written: 6.16 KiB
1 row in set (0.0015 sec)

這個例子中，我使用 sys.io_global_by_wait_by_latency檢視，因為它自動讓延時變得易讀。對壓縮/解壓縮階段的延遲， FORMAT_PICO_TIME()函式對其進行轉換。

這個例子中，MySQL花費了6.21秒來壓縮二進位制日誌，每個事務平均不到400微秒。相比，二進位制日誌檔案執行I/O花費了4.8分鐘。

在啟用壓縮前，應檢查寫入和讀取二進位制日誌檔案花費的時間，以便確定效能變化。您還應該檢查CPU使用變化。

上述輸出，它顯示壓縮比為59%，但是對不同型別的負載呢？

不同負載示例

為確定壓縮的效果，我執行了一系列任務，並比較了壓縮和不壓縮時二進位制日誌的大小。為了進行比較，我還手工壓縮系列測試的中未壓縮的二進位制日誌，以檢視最佳壓縮率（與MySQL使用的每次事務壓縮不同）。除了給定測試所需的設定外，測試都是使用預設配置執行的。

已測試一下負載：

• 批次插入：載入 employees示例資料庫。

• 批次更新：更新了 employees.salaries表中所有行的 salary列： UPDATE employees.salaries SET salary = salary + 1。

• 批次載入：用sysbench為 oltp_read_write基準測試做準備，每個表載入100000行資料。

• OLTP負載：用sysbench執行 oltp_read_write基準測試， --events=15000，使用先前測試的四張表。

• 單行刪除：刪除sysbench測試的一張表的100000行資料。這些行被逐個刪除，這代表壓縮最壞情況，因為事務非常小，每個刪除的行的二進位制日誌中只有前一個映像。

單行刪除可以使用MySQL Shell輕鬆執行，例如使用Python：

from datetime import datetime
for i in range(100000):
    if i % 5000 == 0:
        print('{0}: i = {1}'.format(datetime.now(), i))
    session.run_sql('DELETE FROM sbtest.sbtest1 WHERE id = {0}'.format(i+1))
print('{0}: i = 1000000'.format(datetime.now()))

每個任務生成25M-82M的未壓縮二進位制日誌。

測試採用以下設定：

• 不壓縮

• 啟用壓縮

• 加密但是不壓縮

• 加密並啟用壓縮

• MySQL中不壓縮+用 zstd壓縮

由於MySQL使用的是 Zstandard 壓縮演算法，所以選擇 zstd進行壓縮。如果您想自己嘗試，您可以從Facebook's Github倉庫下載Zstandard原始碼，其中包括編譯說明。

下表列出了每種組合的二進位制日誌的位元組大小。

測試	正常	壓縮	加密	加密+壓縮	zstd
載入僱員庫	66378639	28237933	66379151	28238634	26892387
批次更新	85698320	24667051	85698832	24667677	24779953
sysbench準備	76367740	39221052	76368252	39221806	39775067
sysbench執行	26190200	11933713	26190712	11936561	8468625
單行刪除	47300156	39492400	47300668	39637684	16525219

加密的二進位制日誌壓縮和未加密的二進位制日誌表明壓縮是在加密之前完成的。（加密的資料不能很好地壓縮。）因為是否啟用加密沒有區別，所以只會進一步討論正常的（未壓縮的）、壓縮的和 zstd結果。二進位制日誌的大小可以在圖中看到：

不同壓縮方案的二進位制日誌大小。

毫不意外地，大批次載入和更新是壓縮二進位制日誌最佳效果，分別是未壓縮大小的29%和51%。Sysbench的OLTP負載也可以壓縮到正常的46%。同樣不奇怪的是，壓縮的二進位制日誌大小是未壓縮二進位制日誌的83%，所以單行刪除的壓縮效果幾乎沒有那麼好。

當比較MySQL壓縮的二進位制日誌和使用 zstd手工壓縮的二進位制日誌時，批次負載的檔案大小大致相同，這也反映出對於大事務，按每個事務進行壓縮等同於壓縮整個檔案。當事務大小變小時，每個事務壓縮的相對效率會降低，這對單行刪除尤其明顯。

另一個要考慮的因素是壓縮級別。

舉例-壓縮級別

在壓縮級別上有一些奇怪的地方，簡單來說就是不需要更改設定。

第一個奇怪的地方是，允許的值是1-22，但是 zstd只支援1-19。MySQL文件中沒有解釋兩者區別。

第二個奇怪的地方是，透過改變 binlog_transaction_compression_level_zstd的值，壓縮的二進位制日誌的大小實際上沒有變化，從表中可以看出：

Level/Test	MySQL	-	zstd	-
-	載入	OLTP	載入	OLTP
1	28238142	11935450	34483207	8531545
3	28237933	11933713	26892387	8468625
11	28238128	11902669	24737194	6639524
19	28238246	11937664	18867187	5724300
22	28238125	11910022

分別使用壓縮級別1、3、11、19和包括使用 zstd手工壓縮二進位制日誌。“載入”用於載入僱員資料庫，OLTP是sysbench做 oltp_read_write基準壓測。資料也可以在圖中看到：

二進位制日誌大小與壓縮級別的關係

可以看出，無論MySQL中使用的壓縮級別如何，檔案大小基本上沒有差異，而對於 zstd，隨著壓縮級別的增加，檔案大小如預期一樣減小。對於級別1的載入測試，MySQL壓縮效果甚至比 zstd壓縮效果好。就像從未在MySQL中設定壓縮級別。

一種可能的解釋是，Zstandard支援針對給定資料型別（建立字典）訓練演算法。這特別有助於改進小資料的壓縮。我不知道MySQL是否使用字典，如果使用字典，是否所有的壓縮級別都大致相同。

總結

新的二進位制日誌事務壓縮非常有效，可以很好減少I/O，磁碟使用量和網路使用量。您應該啟用它嗎？

除非CPU非常緊張，否則應該啟用二進位制日誌壓縮。從這些測試中可以看出，二進位制日誌佔用磁碟空間可以大致減少一半，但與往常一樣，它依賴於負載，您應該針對負載進行測試。

您可以在協議級別啟用壓縮傳送二進位制日誌。沒有理由同時啟用二進位制日誌事務壓縮和協議壓縮。

另一方面，目前沒有理由改變壓縮等級。

---

---

About Me

........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在個人微 信公眾號（ DB寶）上有同步更新 ● QQ群號： 230161599 、618766405，微信群私聊 ● 個人QQ號（646634621），微 訊號（db_bao），註明新增緣由 ● 於 2020年11月完成 ● 最新修改時間：2020年11月 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店： ● 小麥苗出版的資料庫類叢書： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用、DBA學習班： http://blog.itpub.net/26736162/viewspace-2148098/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ........................................................................................................................ 請掃描下面的二維碼來關注小麥苗的微 信公眾號（ DB寶）及QQ群（230161599、618766405）、新增小麥苗微 信（db_bao）， 學習最實用的資料庫技術。 ........................................................................................................................