揪出MySQL延遲上千秒的元兇

揪出MySQL延遲上千秒的元兇

背景

Part1:寫(xiě)在最前

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

Part2:重點(diǎn)參數(shù)分析

binlog_format

眾所周知，binlog_format是設(shè)置binlog格式的參數(shù)，我們可以配置為STATEMENT、MIXED、ROW三種格式，可以動(dòng)態(tài)調(diào)節(jié)。三種格式各有有缺。我們的線上生產(chǎn)庫(kù)統(tǒng)一配置的是MIXED格式。MIXED格式會(huì)在STATEMENT格式和ROW格式中根據(jù)場(chǎng)景不同來(lái)使用不同的格式進(jìn)行切換。

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

Part3:知識(shí)儲(chǔ)備

對(duì)于MIXED格式來(lái)說(shuō)，在如下情況的時(shí)候，binlog會(huì)自動(dòng)轉(zhuǎn)為ROW格式記錄

1.NDB引擎

2.SQL語(yǔ)句里包含了UUID()函數(shù)。

3.自增長(zhǎng)字段被更新了。

4.包含了insert delayed語(yǔ)句。

5.使用了用戶定義函數(shù)（UDF）。

6.使用了臨時(shí)表。

7.？還有一種情況會(huì)導(dǎo)致mixed格式轉(zhuǎn)換為ROW，本文會(huì)加以復(fù)現(xiàn)。

實(shí)戰(zhàn)

Part1:監(jiān)控

我們看出，在凌晨2點(diǎn)的時(shí)候，從庫(kù)的延遲陡增，而此時(shí)從庫(kù)的機(jī)器負(fù)載和網(wǎng)卡并未達(dá)到瓶頸。

Part2:延遲原因分析

我們可以看出，從2點(diǎn)06起，binlog刷新非?？欤旧蠋资刖涂梢詫?xiě)滿一個(gè)1.1GB的binlog文件。這樣基本就能夠確定，是因?yàn)閷?xiě)入量過(guò)大導(dǎo)致的。

寫(xiě)入量過(guò)大又有兩種情況：

1. 單純的業(yè)務(wù)量激增，QPS增長(zhǎng)引起；

2. binlog轉(zhuǎn)為了ROW格式導(dǎo)致存儲(chǔ)內(nèi)容激增引起。

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

Part3:rootcase

delete from tablename where xxxx limit 100;

這種語(yǔ)法會(huì)將MIXED格式的binlog，轉(zhuǎn)為ROW格式記錄，而筆者案例中的這張表包含TEXT大字段，每次delete都會(huì)把整個(gè)TEXT大字段帶入binlog，進(jìn)而導(dǎo)致binlog激增，從庫(kù)追不上主庫(kù)產(chǎn)生延遲的情況。

Part4:解決辦法

根本原因找到后，解決起來(lái)就得心應(yīng)手了，找到相關(guān)開(kāi)發(fā)，去掉delete from table where xxx limit 這種用法，就能夠避免row格式的記錄。

Warning:警告其實(shí)，delete/update limit、insert .....select limit這種用法是危險(xiǎn)的，很容易產(chǎn)生問(wèn)題。真的要使用這種這種方法的話，也需要結(jié)合order by語(yǔ)句來(lái)保證limit的有效性。

遇到此類語(yǔ)句時(shí)：

當(dāng)使用STATEMENT模式時(shí)，會(huì)發(fā)出一個(gè)警告，說(shuō)明語(yǔ)句對(duì)于基于語(yǔ)句的復(fù)制是不安全的。

當(dāng)使用STATEMENT模式時(shí)，即使它們也有一個(gè)ORDER BY子句（因此是確定性的），也會(huì)為包含LIMIT的DML語(yǔ)句發(fā)出警告。 這是一個(gè)已知的問(wèn)題。 （BUG＃42851）

當(dāng)使用MIXED模式時(shí)，語(yǔ)句使用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)

可以看出，在官方文檔中，何時(shí)MIXED格式會(huì)轉(zhuǎn)換為ROW格式中，并未提到limit語(yǔ)句會(huì)將MIXED格式轉(zhuǎn)換為ROW，國(guó)內(nèi)不少書(shū)籍和博客上也未有提及，本文記錄這個(gè)案例，希望對(duì)遇到這個(gè)問(wèn)題和未來(lái)可能遇到這個(gè)問(wèn)題的讀者能夠節(jié)省處理時(shí)間，盡快定位到根源。

官方文檔對(duì)于MIXED格式在使用limit語(yǔ)法時(shí)轉(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é)——

通過(guò)這個(gè)案例，我們能夠了解到什么情況下binlog_format會(huì)由MIXED格式轉(zhuǎn)為ROW格式，以及常見(jiàn)的延遲原因和解決辦法。由于筆者的水平有限，編寫(xiě)時(shí)間也很倉(cāng)促，文中難免會(huì)出現(xiàn)一些錯(cuò)誤或者不準(zhǔn)確的地方，不妥之處懇請(qǐng)讀者批評(píng)指正。喜歡筆者的文章，右上角點(diǎn)一波關(guān)注，謝謝！