MySQL從庫延遲故障

編者按：筆者此次遇到某客戶的MySQL數(shù)據(jù)庫出現(xiàn)了Slave庫操作延遲的故障，給數(shù)據(jù)的查詢操作帶來了很大的影響，最終判斷是向其中添加大量的數(shù)據(jù)導(dǎo)致從節(jié)點(diǎn)難以應(yīng)對。

某單位的管理系統(tǒng)后端采用的是MySQL數(shù)據(jù)庫，安裝的都是CentOS 7.X，硬件配置為八核心的CPU，32 GB內(nèi)存。

為了提高性能，系統(tǒng)配置了MySQL主從復(fù)制架構(gòu)。Master庫主要執(zhí)行數(shù)據(jù)的寫入操作，Slave庫主要提供屬于的查詢操作。

該系統(tǒng)最近以來，出現(xiàn)了Slave庫操作延遲的故障，表現(xiàn)為延遲將近一個小時后才恢復(fù)正常，這給數(shù)據(jù)的查詢操作帶來了不利的影響。

故障排查

對于MySQl主從復(fù)制延遲故障來說，在很多情況下都是因?yàn)橹鲙斓腄ML操作引發(fā)的。例如，當(dāng)主庫存在慢查詢操作，自然會引起從庫的延遲。

但是筆者觀察主庫的慢查詢?nèi)罩?，并沒有在發(fā)生從庫延遲的時間段內(nèi)發(fā)現(xiàn)慢查詢操作，這說明該問題和主庫的慢查詢無關(guān)。對從庫的日志信息進(jìn)行查看，也沒有發(fā)現(xiàn)執(zhí)行過慢查詢語句的情況。

慢查詢?nèi)罩静⒎侵粫涗泩?zhí)行比較慢的DML語句，只要DML語句的執(zhí)行超過了指定的時間，都可以稱之為慢查詢。默認(rèn)設(shè)置下，執(zhí)行超過10 s的語句才會被記錄到慢查詢?nèi)罩局?。在MySQL控制臺中執(zhí)行“show global status like'%slow_queries%'”命令，查看從MySQL服務(wù)啟動以后慢查詢語句的總量。

打開“/data/mysql/auditlogs/server_audit.log”文件，查看MySQL的審計日志，發(fā)現(xiàn)在延遲的時間段內(nèi)出現(xiàn)了大量的并發(fā)插入操作，這些數(shù)量很大的插入操作很有可能引發(fā)從庫的延遲故障。

要想深入查看MySQL歷史操作信息，還必須依靠其二進(jìn)制日志來實(shí)現(xiàn)。在二進(jìn)制日志中記錄了MySQL的所有DDL和DML語句信息，當(dāng)然，對于Select語句來說是不記錄的。對于所記錄的每條語句，還會顯示其消耗的時間。根據(jù)這些內(nèi)容，就可以清晰的了解上述插入語句的相關(guān)執(zhí)行情況。

使用VI等工具是無法查看MySQL的二進(jìn)制日志的，必須使用自帶的mysqlbinlog工具來查看。

例如，執(zhí)行“mysqlbinlog mysql-bin.00000x”命令，可以查看指定的二進(jìn)制日志內(nèi)容，其中的“x”表示具體的編號。因?yàn)槲覀冎皇菍Πl(fā)生從庫延遲的時間段看，所以需要過濾該時間段內(nèi)的日志信息。

執(zhí)行“mysqlbinlog--no-defaults --startdatetime='2020-01-10 10:20:00' --stop-datetime='2020-01-10 11:10:00' --base64-output=decoderows -vv mysql-bin.00xxxx> sql.txt”命令，將指定時間段的日志信息導(dǎo)出到目標(biāo)文件中。

因?yàn)樾枰樵冊摃r間段的插入語句執(zhí)行情況，所以執(zhí)行“cat mysql.txt|grep-v SET |grep INSERT|wc-l”命令，來過濾其中的插入語句的數(shù)量信息。根據(jù)查詢到的結(jié)構(gòu)，發(fā)現(xiàn)在該時間段內(nèi)執(zhí)行了500多萬次的數(shù)據(jù)Insert操作。毫無疑問，大量的Insert操作自然會導(dǎo)致從庫應(yīng)接不暇，出現(xiàn)延遲故障就不足為奇了。

接下來需要探查究竟是哪些數(shù)據(jù)表進(jìn)行了大量的插入操作，就可以準(zhǔn)確的判斷問題的根源。

使 用“iostat” “iotop”命令，可以幫助用戶了解磁盤I/O信息。對于數(shù)據(jù)表的I/O情況進(jìn)行分析，就需要使用到“pt-ioprofile”這款實(shí)用工具，該工具包含在Percona-Toolkit軟件包 中，執(zhí) 行“yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IOSocket-SSL” “yum install https://www.percona.com/redir/downloads/perconarelease/redhat/0.1-6/percona-release-0.1-6.noarch.rpm” “yum install percona-toolkit”命令，來安裝該工具包。

當(dāng)從庫再次出現(xiàn)延遲故障時，執(zhí)行“pt-ioprofile--profile-process=my sqld --run-time=120--save-samples=mysql_ioprofile.txt --groupby=filename --cell=sizes--aggregate=avg”命令，來收集其120 s內(nèi)，MySQL數(shù)據(jù)庫中存在I/O操作的文件信息，以及各種調(diào)用平均每秒產(chǎn)生的數(shù)據(jù)量。

其中“profile-process”參數(shù)為指定MySQL的進(jìn)程名，“run-time”參數(shù)指定采集時間，“save-samples”參數(shù)指定輸出文件名稱，“-groupby”參數(shù)執(zhí)行聚合的方式，這里采用的文件聚合方式。“cell”參數(shù)指定系統(tǒng)調(diào)用傳輸數(shù)據(jù)量，“aggregate”參數(shù)指定計算平均值。

并且在返回信息中的“filename”列中顯示MySQL各數(shù)據(jù)表和日志名稱，在“total”列中顯示了對應(yīng)的在指定時間中產(chǎn)生的數(shù)據(jù)量，在“pread” “pwrite”以及“fsync”列中顯示了對應(yīng)的讀取、寫入和同步的數(shù)據(jù)量。

根據(jù)這些信息，可以看出究竟哪些數(shù)據(jù)表產(chǎn)生的I/O數(shù)據(jù)量最大。

故障解決

根據(jù)以上分析，可以看到在MySQL中名為“cdrdat”和“docutk1”的數(shù)據(jù)表產(chǎn)生的I/O量很大。在這兩個表中存儲了大量的基礎(chǔ)數(shù)據(jù)。執(zhí)行“crontab -l”命令，查看當(dāng)前用戶的定時任務(wù)信息，發(fā)現(xiàn)其中存在幾個腳本文件，其作用就是按照指定的時間，向上述MySQL中添加大量的數(shù)據(jù)。

筆者了解到，由于該管理系統(tǒng)正在進(jìn)行優(yōu)化和調(diào)整，需要向其中添加大量的數(shù)據(jù)。因此，開發(fā)部門人員為了提高效率，就編寫了相應(yīng)的腳本程序，利用計劃任務(wù)定時向MySQL中插入大量的數(shù)據(jù)。

但是數(shù)據(jù)庫管理員人員對其并不了解，大量的數(shù)據(jù)插入操作必然導(dǎo)致主節(jié)點(diǎn)的磁盤I/O處于忙碌狀態(tài)，在指定的時間段向從節(jié)點(diǎn)寫入大量數(shù)據(jù)，導(dǎo)致從節(jié)點(diǎn)難以應(yīng)對，才出現(xiàn)了以上延遲的問題。

處理的方法是，對上述腳本進(jìn)行優(yōu)化，使其在多個時間段內(nèi)分批向MySQL插入數(shù)據(jù)，來降低對MySQL的數(shù)據(jù)寫入的壓力，以保證MySQL主從復(fù)制結(jié)構(gòu)可以正常運(yùn)作。

針對上述故障，可以看出當(dāng)MySQL出現(xiàn)較大的訪問量時，必須對其進(jìn)行優(yōu)化處理，才可以避免故障的發(fā)生。一般來說，可以使用主從復(fù)制，讀寫分離和負(fù)載均衡等方法來實(shí)現(xiàn)。對于SQL語句進(jìn)行優(yōu)化，也可以有效的提高數(shù)據(jù)庫運(yùn)行效率。

因?yàn)镾QL語句編寫平庸，沒有使用索引等問題，同樣會導(dǎo)致數(shù)據(jù)庫性能低下。例如，建立索引可以提高查詢速度，在諸如Where、Order by以及Group by等語句涉及的列上建立索引，來提高查詢速度等。使用Explain可以選擇更好的索引和優(yōu)化查詢語句，它基于圖形化或基于文本的方式，詳細(xì)說明了SQL語句的每個部分的執(zhí)行情況，通過選擇更好的索引列，對費(fèi)時較長SQL語句進(jìn)行優(yōu)化，來提高查詢速度。

對于體積較大的數(shù)據(jù)表來說，可以采取分表的辦法，來降低數(shù)據(jù)庫的數(shù)據(jù)庫的負(fù)擔(dān)提高查詢的效能。對于數(shù)據(jù)量很大但是訪問的用戶很少的情況，可以采取分表的方法加以應(yīng)對。對于數(shù)據(jù)量不大但訪問用戶很多的情況，才可以采取分庫的方法，來解決數(shù)據(jù)庫端并發(fā)量大的問題。