揪出MySQL延遲上千秒的元兇
背景
Part1:寫在最前
MySQL的延遲告警想必大家一定不陌生，MySQL引起從庫延遲的原因有很多，從硬件上講可能是網(wǎng)卡，磁盤，內(nèi)存達到瓶頸，從數(shù)據(jù)庫層面來講，可能是SQL效率低下，或者大批量寫入引起的。本文的案例將剖析一個由binlog格式引發(fā)的延遲問題，看完本文，再遇到這類告警的時候，相信你可以瞬間定位到問題所在！
Part2:重點參數(shù)分析
| Property | Value |
|---|---|
| Command-Line Format | --binlog-format=format |
| System Variable | binlog_format |
| Scope | Global, Session |
| Dynamic | Yes |
| Type (>= 5.5.31-ndb-7.2.13) | enumeration |
| Type (>= 5.5.15-ndb-7.2.1, <= 5.5.30-ndb-7.2.12) | enumeration |
| Type | enumeration |
| Default (>= 5.5.31-ndb-7.2.13) | MIXED |
| Default (>= 5.5.15-ndb-7.2.1, <= 5.5.30-ndb-7.2.12) | STATEMENT |
| Default | STATEMENT |
| Valid Values (>= 5.5.31-ndb-7.2.13) |
|
| Valid Values (>= 5.5.15-ndb-7.2.1, <= 5.5.30-ndb-7.2.12) |
|
| Valid Values |
|
眾所周知，binlog_format是設(shè)置binlog格式的參數(shù)，我們可以配置為STATEMENT、MIXED、ROW三種格式，可以動態(tài)調(diào)節(jié)。三種格式各有有缺。我們的線上生產(chǎn)庫統(tǒng)一配置的是MIXED格式。MIXED格式會在STATEMENT格式和ROW格式中根據(jù)場景不同來使用不同的格式進行切換。
- mysql> show global variables like 'binlog_format';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | binlog_format | MIXED |
- +---------------+-------+
- 1 row in set (0.08 sec)
Part3:知識儲備
對于MIXED格式來說，在如下情況的時候，binlog會自動轉(zhuǎn)為ROW格式記錄
1.NDB引擎
2.SQL語句里包含了UUID()函數(shù)。
3.自增長字段被更新了。
4.包含了insert delayed語句。
5.使用了用戶定義函數(shù)（UDF）。
6.使用了臨時表。
7.？還有一種情況會導(dǎo)致mixed格式轉(zhuǎn)換為ROW，本文會加以復(fù)現(xiàn)。
實戰(zhàn)
Part1:監(jiān)控
我們看出，在凌晨2點的時候，從庫的延遲陡增，而此時從庫的機器負(fù)載和網(wǎng)卡并未達到瓶頸。
Part2:延遲原因分析
我們可以看出，從2點06起，binlog刷新非?？?，基本上幾十秒就可以寫滿一個1.1GB的binlog文件。這樣基本就能夠確定，是因為寫入量過大導(dǎo)致的。
寫入量過大又有兩種情況：
- 單純的業(yè)務(wù)量激增，QPS增長引起；
- binlog轉(zhuǎn)為了ROW格式導(dǎo)致存儲內(nèi)容激增引起。
我們使用pt工具pt-query-digest或者命令行，都能夠分析出binlog做了哪些操作。使用pt-query-digest的話可以結(jié)合mysqlbinlog命令，對日志進行分析。
Part3:rootcase
- delete from tablename where xxxx limit 100;
這種語法會將MIXED格式的binlog，轉(zhuǎn)為ROW格式記錄，而筆者案例中的這張表包含TEXT大字段，每次delete都會把整個TEXT大字段帶入binlog，進而導(dǎo)致binlog激增，從庫追不上主庫產(chǎn)生延遲的情況。
Part4:解決辦法
根本原因找到后，解決起來就得心應(yīng)手了，找到相關(guān)開發(fā)，去掉delete from table where xxx limit 這種用法，就能夠避免row格式的記錄。
Warning:警告其實，delete/update limit、insert .....select limit這種用法是危險的，很容易產(chǎn)生問題。真的要使用這種這種方法的話，也需要結(jié)合order by語句來保證limit的有效性。
遇到此類語句時：
當(dāng)使用STATEMENT模式時，會發(fā)出一個警告，說明語句對于基于語句的復(fù)制是不安全的。
當(dāng)使用STATEMENT模式時，即使它們也有一個ORDER BY子句（因此是確定性的），也會為包含LIMIT的DML語句發(fā)出警告。 這是一個已知的問題。 （BUG＃42851）
當(dāng)使用MIXED模式時，語句使用row的模式復(fù)制。
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格式會轉(zhuǎn)換為ROW格式中，并未提到limit語句會將MIXED格式轉(zhuǎn)換為ROW，國內(nèi)不少書籍和博客上也未有提及，本文記錄這個案例，希望對遇到這個問題和未來可能遇到這個問題的讀者能夠節(jié)省處理時間，盡快定位到根源。
官方文檔對于MIXED格式在使用limit語法時轉(zhuǎn)換為ROW格式記錄在其他章節(jié)，是如下描述的：
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.
總結(jié)
通過這個案例，我們能夠了解到什么情況下binlog_format會由MIXED格式轉(zhuǎn)為ROW格式，以及常見的延遲原因和解決辦法。由于筆者的水平有限，編寫時間也很倉促，文中難免會出現(xiàn)一些錯誤或者不準(zhǔn)確的地方，不妥之處懇請讀者批評指正。
