揪出MySQL延遲上千秒的元兇

背景

Part1:寫在最前

成都創(chuàng)新互聯(lián)公司專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于網(wǎng)站制作、做網(wǎng)站、塔城網(wǎng)絡(luò)推廣、小程序開發(fā)、塔城網(wǎng)絡(luò)營銷、塔城企業(yè)策劃、塔城品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們大的嘉獎；成都創(chuàng)新互聯(lián)公司為所有大學生創(chuàng)業(yè)者提供塔城建站搭建服務(wù)，24小時服務(wù)熱線：18982081108，官方網(wǎng)址：www.cdcxhl.com

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

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

binlog_format

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)	ROW STATEMENT MIXED
Valid Values (>= 5.5.15-ndb-7.2.1, <= 5.5.30-ndb-7.2.12)	ROW STATEMENT MIXED
Valid Values	ROW STATEMENT MIXED

眾所周知，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點的時候，從庫的延遲陡增，而此時從庫的機器負載和網(wǎng)卡并未達到瓶頸。

Part2:延遲原因分析

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

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

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

2. 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的有效性。

遇到此類語句時：

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

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

當使用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)一些錯誤或者不準確的地方，不妥之處懇請讀者批評指正。喜歡筆者的文章，右上角點一波關(guān)注，謝謝！

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機、免備案服務(wù)器”等云主機租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。

網(wǎng)站名稱：揪出MySQL延遲上千秒的元兇-創(chuàng)新互聯(lián)
本文路徑：http://weahome.cn/article/dejggg.html