揪出MySQL延遲上千秒的元兇

揪出MySQL延遲上千秒的元兇

背景

Part1:寫在最前

MySQL的延遲告警想必大家一定不陌生，MySQL引起從庫延遲的原因有很多，從硬件上講可能是網卡，磁盤，內存達到瓶頸，從數據庫層面來講，可能是SQL效率低下，或者大批量寫入引起的。本文的案例將剖析一個由binlog格式引發的延遲問題，看完本文，再遇到這類告警的時候，相信你可以瞬間定位到問題所在！

Part2:重點參數分析

binlog_format

眾所周知，binlog_format是設置binlog格式的參數，我們可以配置為STATEMENT、MIXED、ROW三種格式，可以動態調節。三種格式各有有缺。我們的線上生產庫統一配置的是MIXED格式。MIXED格式會在STATEMENT格式和ROW格式中根據場景不同來使用不同的格式進行切換。

mysql> show global variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.08 sec)

Part3:知識儲備

對于MIXED格式來說，在如下情況的時候，binlog會自動轉為ROW格式記錄

1.NDB引擎

2.SQL語句里包含了UUID()函數。

3.自增長字段被更新了。

4.包含了insert delayed語句。

5.使用了用戶定義函數（UDF）。

6.使用了臨時表。

7.？還有一種情況會導致mixed格式轉換為ROW，本文會加以復現。

實戰

Part1:監控

我們看出，在凌晨2點的時候，從庫的延遲陡增，而此時從庫的機器負載和網卡并未達到瓶頸。

Part2:延遲原因分析

我們可以看出，從2點06起，binlog刷新非常快，基本上幾十秒就可以寫滿一個1.1GB的binlog文件。這樣基本就能夠確定，是因為寫入量過大導致的。

寫入量過大又有兩種情況：

1. 單純的業務量激增，QPS增長引起；

2. binlog轉為了ROW格式導致存儲內容激增引起。

我們使用pt工具pt-query-digest或者命令行，都能夠分析出binlog做了哪些操作。使用pt-query-digest的話可以結合mysqlbinlog命令，對日志進行分析。

Part3:rootcase

delete from tablename where xxxx limit 100;

這種語法會將MIXED格式的binlog，轉為ROW格式記錄，而筆者案例中的這張表包含TEXT大字段，每次delete都會把整個TEXT大字段帶入binlog，進而導致binlog激增，從庫追不上主庫產生延遲的情況。

Part4:解決辦法

根本原因找到后，解決起來就得心應手了，找到相關開發，去掉delete from table where xxx limit 這種用法，就能夠避免row格式的記錄。

Warning:警告其實，delete/update limit、insert .....select limit這種用法是危險的，很容易產生問題。真的要使用這種這種方法的話，也需要結合order by語句來保證limit的有效性。

遇到此類語句時：

當使用STATEMENT模式時，會發出一個警告，說明語句對于基于語句的復制是不安全的。

當使用STATEMENT模式時，即使它們也有一個ORDER BY子句（因此是確定性的），也會為包含LIMIT的DML語句發出警告。 這是一個已知的問題。 （BUG＃42851）

當使用MIXED模式時，語句使用row的模式復制。

Part5:官方文檔

When running in MIXED logging format, the server automatically switches from statement-based to row-based logging under the following conditions:
When a DML statement updates an NDBCLUSTER table.
When a function contains UUID().
When one or more tables with AUTO_INCREMENT columns are updated and a trigger or stored function is invoked. Like all other unsafe statements, this generates a warning if binlog_format = STATEMENT.
When any INSERT DELAYED is executed.
When a call to a UDF is involved.
If a statement is logged by row and the session that executed the statement has any temporary tables, logging by row is used for all subsequent statements (except for those accessing temporary tables) until all temporary tables in use by that session are dropped.
This is true whether or not any temporary tables are actually logged.
Temporary tables cannot be logged using row-based format; thus, once row-based logging is used, all subsequent statements using that table are unsafe. The server approximates this condition by treating all statements executed during the session as unsafe until the session no longer holds any temporary tables.
When FOUND_ROWS() or ROW_COUNT() is used. (Bug #12092, Bug #30244)
When USER(), CURRENT_USER(), or CURRENT_USER is used. (Bug #28086)
When a statement refers to one or more system variables. (Bug #31168)

可以看出，在官方文檔中，何時MIXED格式會轉換為ROW格式中，并未提到limit語句會將MIXED格式轉換為ROW，國內不少書籍和博客上也未有提及，本文記錄這個案例，希望對遇到這個問題和未來可能遇到這個問題的讀者能夠節省處理時間，盡快定位到根源。

官方文檔對于MIXED格式在使用limit語法時轉換為ROW格式記錄在其他章節，是如下描述的：

Statement-based replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT statements is unsafe since the order of the rows affected is not defined. (Such statements can be replicated correctly with statement-based replication only if they also contain an ORDER BY clause.) When such a statement is encountered:

• When using STATEMENT mode, a warning that the statement is not safe for statement-based replication is now issued.

  When using STATEMENT mode, warnings are issued for DML statements containing LIMIT even when they also have an ORDER BY clause (and so are made deterministic). This is a known issue. (Bug #42851)

• When using MIXED mode, the statement is now automatically replicated using row-based mode.

——總結——

通過這個案例，我們能夠了解到什么情況下binlog_format會由MIXED格式轉為ROW格式，以及常見的延遲原因和解決辦法。由于筆者的水平有限，編寫時間也很倉促，文中難免會出現一些錯誤或者不準確的地方，不妥之處懇請讀者批評指正。喜歡筆者的文章，右上角點一波關注，謝謝！