除了 MySQL 本身之外，如何分析定位其他因素的可能性？
作者：龔唐杰，愛(ài)可生 DBA 團(tuán)隊(duì)成員，主要負(fù)責(zé) MySQL 技術(shù)支持，擅長(zhǎng) MySQL、PG、國(guó)產(chǎn)數(shù)據(jù)庫(kù)。
愛(ài)可生開(kāi)源社區(qū)出品，原創(chuàng)內(nèi)容未經(jīng)授權(quán)不得隨意使用，轉(zhuǎn)載請(qǐng)聯(lián)系小編并注明來(lái)源。
本文約 1200 字，預(yù)計(jì)閱讀需要 3 分鐘。
背景
在執(zhí)行跑批任務(wù)的過(guò)程中，應(yīng)用程序遇到了一個(gè)問(wèn)題：部分任務(wù)的數(shù)據(jù)庫(kù)連接會(huì)突然丟失，導(dǎo)致任務(wù)無(wú)法完成。從數(shù)據(jù)庫(kù)的錯(cuò)誤日志中，發(fā)現(xiàn)了 Aborted connection 的信息，這說(shuō)明客戶端和服務(wù)器之間的通信被異常中斷了。
分析
為了找出問(wèn)題的原因，我們首先根據(jù)經(jīng)驗(yàn)，分析了可能導(dǎo)致連接被 Aborted 的幾種常見(jiàn)情況：
- 客戶端沒(méi)有正確地關(guān)閉連接，沒(méi)有調(diào)用
mysql_close()函數(shù)。 - 客戶端空閑時(shí)間超過(guò)了
wait_timeout或interactive_timeout參數(shù)的秒數(shù)，服務(wù)器自動(dòng)斷開(kāi)了連接。 - 客戶端發(fā)送或接收的數(shù)據(jù)包大小超過(guò)了
max_allowed_packet參數(shù)的值，導(dǎo)致連接中斷。 - 客戶端試圖訪問(wèn)數(shù)據(jù)庫(kù)，但沒(méi)有權(quán)限，或者使用了錯(cuò)誤的密碼，或者連接包不包含正確的信息。
然而，經(jīng)過(guò)排查，發(fā)現(xiàn)以上情況都不適用于當(dāng)前的問(wèn)題。因?yàn)槿蝿?wù)在之前都是正常運(yùn)行的，而且程序也沒(méi)有變動(dòng)，所以可以排除第一種情況。查看了 MySQL 的超時(shí)參數(shù) wait_timeout 和 interactive_timeout ，發(fā)現(xiàn)它們都是 28800，也就是 8 個(gè)小時(shí)，遠(yuǎn)遠(yuǎn)超過(guò)了任務(wù)執(zhí)行時(shí)間，所以可以排除第二種情況。也檢查了客戶端和服務(wù)器的 max_allowed_packet 參數(shù)，發(fā)現(xiàn)它們都是 64M，也不太可能超過(guò)這個(gè)限制，所以可以排除第三種情況。我們也確認(rèn)了客戶端的數(shù)據(jù)庫(kù)訪問(wèn)權(quán)限，密碼，連接包等信息，都是正確的，所以可以排除第四種情況。
到此，我們初步感覺(jué) MySQL 層面應(yīng)該沒(méi)有問(wèn)題，問(wèn)題可能出在其他地方。
為了進(jìn)一步定位問(wèn)題，我們嘗試了修改服務(wù)器的一些相關(guān)內(nèi)核參數(shù)，如下：
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_time = 120
net.core.rmem_default = 2097152
net.core.wmem_default = 2097152
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_max_syn_backlog = 16384這些參數(shù)主要是為了優(yōu)化網(wǎng)絡(luò)連接的性能和穩(wěn)定性，避免連接被意外關(guān)閉或超時(shí)。但是，修改后的結(jié)果并沒(méi)有改善，連接還是會(huì)異常中斷。
最后，我們嘗試了進(jìn)行抓包分析，通過(guò) Wireshark 工具，我們發(fā)現(xiàn)了一個(gè)異常的現(xiàn)象：服務(wù)器會(huì)給客戶端發(fā)送大量的 ACK 包。如下圖所示：
這些 ACK 包是 TCP 協(xié)議中的確認(rèn)包，表示服務(wù)器已經(jīng)收到了客戶端的數(shù)據(jù)包，請(qǐng)求客戶端繼續(xù)發(fā)送數(shù)據(jù)。但是，為什么服務(wù)器會(huì)發(fā)送這么多的 ACK 包呢？我們猜測(cè)可能是網(wǎng)絡(luò)有異常，導(dǎo)致客戶端接收不到服務(wù)器返回的 ACK 包，所以服務(wù)器會(huì)反復(fù)發(fā)送 ACK 包，直到超時(shí)或收到客戶端的響應(yīng)。但是，經(jīng)過(guò)網(wǎng)絡(luò)人員的排查，未發(fā)現(xiàn)有明顯的問(wèn)題。
繼續(xù)分析抓包，我們又發(fā)現(xiàn)了另一個(gè)異常的現(xiàn)象：客戶端會(huì)給發(fā)送服務(wù)器一些窗口警告。如下圖所示：
這些窗口警告是 TCP 協(xié)議中的流量控制機(jī)制，表示服務(wù)器或客戶端的接收窗口已經(jīng)滿了，不能再接收更多的數(shù)據(jù)。
[TCP Window Full] 是發(fā)送端向接收端發(fā)送的一種窗口警告，表示已經(jīng)到數(shù)據(jù)接收端的極限了
[TCP ZeroWindow] 是接收端向發(fā)送端發(fā)送的一種窗口警告，告訴發(fā)送者，接收端接收窗口已滿，暫時(shí)停止發(fā)送。
根據(jù)以上信息，我們推測(cè)出了問(wèn)題的原因：由于 MySQL 需要發(fā)送的數(shù)據(jù)太大，客戶端的 TCP 緩存已經(jīng)滿了，所以需要等待客戶端把 TCP 緩存里面的數(shù)據(jù)消化掉，才能繼續(xù)接收數(shù)據(jù)。但是，在這段時(shí)間內(nèi)，MySQL 會(huì)一直向客戶端請(qǐng)求繼續(xù)發(fā)送數(shù)據(jù)，如果客戶端在一定時(shí)間內(nèi)（默認(rèn)是 60 秒）沒(méi)有響應(yīng)，MySQL 就會(huì)認(rèn)為發(fā)送數(shù)據(jù)超時(shí)，中斷了連接。
為了驗(yàn)證推測(cè)，查看 MySQL 的慢日志，發(fā)現(xiàn)了很多 Last_errno: 1161 的記錄。
這些記錄表示 MySQL 在發(fā)送數(shù)據(jù)時(shí)遇到了超時(shí)錯(cuò)誤，而且發(fā)現(xiàn)出現(xiàn)的次數(shù)和應(yīng)用程序失敗的任務(wù)數(shù)很接近。根據(jù) MySQL 官網(wǎng)的說(shuō)明，這個(gè)錯(cuò)誤的含義是：
Error number: 1161; Symbol: ER_NET_WRITE_INTERRUPTED; SQLSTATE: 08S01
Message: Got timeout writing communication packets
可知這個(gè)表示的意思是網(wǎng)絡(luò)寫(xiě)入中斷，而MySQL層面有個(gè)參數(shù)就是控制這個(gè)的，所以嘗試更改net_write_timeout參數(shù)為600，跑批任務(wù)正常運(yùn)行。
所以 MySQL 連接被異常中斷的原因在于客戶端獲取的數(shù)據(jù)庫(kù)太大，超過(guò)了客戶端 TCP 緩存，客戶端需要先處理緩存中的數(shù)據(jù)，在這段時(shí)間內(nèi)，MySQL 會(huì)一直向客戶端請(qǐng)求繼續(xù)發(fā)送數(shù)據(jù)，但是客戶端 60 秒內(nèi)一直未能響應(yīng)，導(dǎo)致 MySQL 發(fā)送數(shù)據(jù)超時(shí)，中斷了連接。
結(jié)論
通過(guò)上述的分析和嘗試，我們得出了以下的結(jié)論：
- 抓包信息中，有很多 ACK 信息是因?yàn)榭蛻舳说木彺鏉M了不能及時(shí)給服務(wù)端反饋，所以服務(wù)器會(huì)反復(fù)發(fā)送 ACK 信息，直到超過(guò) 60秒（
net_write_timeout默認(rèn)值是 60），導(dǎo)致 MySQL 把連接中斷了。 - 慢日志中，有很多 Last_errno: 1161 的記錄，是因?yàn)樵?SQL 實(shí)際已經(jīng)在 MySQL 中執(zhí)行完畢了，但是在發(fā)送數(shù)據(jù)到客戶端時(shí)，由于數(shù)據(jù)量太大超過(guò)了客戶端的 TCP 緩存，然后客戶端上的應(yīng)用在 60 秒內(nèi)未把緩存中的數(shù)據(jù)處理掉，導(dǎo)致 MySQL 往客戶端發(fā)送數(shù)據(jù)超時(shí)。
- MySQL 層面調(diào)整
net_write_timeout參數(shù)只能緩解這個(gè)現(xiàn)象，根因在于單個(gè) SQL 獲取的數(shù)據(jù)量太大，超過(guò)了客戶端的緩存大小，應(yīng)用程序不能短時(shí)間內(nèi)處理完緩存中的數(shù)據(jù)，進(jìn)而導(dǎo)致后續(xù)的數(shù)據(jù)發(fā)送超時(shí)。
優(yōu)化建議
- 業(yè)務(wù)層面進(jìn)行分批處理數(shù)據(jù)，避免單個(gè) SQL 從服務(wù)器獲取大量的數(shù)據(jù)，導(dǎo)致客戶端的 TCP 緩存不足。
- 提高 MySQL 中的
net_write_timeout參數(shù)或者增加客戶端的 TCP 緩存，可緩解此情況的發(fā)生，但不能徹底解決該問(wèn)題，因?yàn)閿?shù)據(jù)量太大仍然會(huì)影響性能和穩(wěn)定性。 - 優(yōu)化 SQL 語(yǔ)句，減少不必要的數(shù)據(jù)返回，比如使用 LIMIT、WHERE 等條件，或者使用聚合函數(shù)，分組函數(shù)等，以減少數(shù)據(jù)量和提高查詢效率。
更多技術(shù)文章，請(qǐng)?jiān)L問(wèn)：https://opensource.actionsky.com/文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-860530.html
關(guān)于 SQLE
SQLE 是一款全方位的 SQL 質(zhì)量管理平臺(tái)，覆蓋開(kāi)發(fā)至生產(chǎn)環(huán)境的 SQL 審核和管理。支持主流的開(kāi)源、商業(yè)、國(guó)產(chǎn)數(shù)據(jù)庫(kù)，為開(kāi)發(fā)和運(yùn)維提供流程自動(dòng)化能力，提升上線效率，提高數(shù)據(jù)質(zhì)量。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-860530.html
SQLE 獲取
| 類(lèi)型 | 地址 |
|---|---|
| 版本庫(kù) | https://github.com/actiontech/sqle |
| 文檔 | https://actiontech.github.io/sqle-docs/ |
| 發(fā)布信息 | https://github.com/actiontech/sqle/releases |
| 數(shù)據(jù)審核插件開(kāi)發(fā)文檔 | https://actiontech.github.io/sqle-docs/docs/dev-manual/plugins/howtouse |
到了這里，關(guān)于一則 TCP 緩存超負(fù)荷導(dǎo)致的 MySQL 連接中斷的案例分析的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
