找出MySQL主從延遲的原因

下面講講關于MySQL主從延遲的原因，文字的奧妙在于貼近主題相關。所以，閑話就不談了，我們直接看下文吧，相信看完MySQL主從延遲的原因這篇文章你一定會有所受益。

Step1 : iostat 查看IO情況

 iostat -x 1 查看IO情況,哪個磁盤的IO負載較高，接下來我們就來定位具體的負載來源

Step2： iotop定位負載來源進程

 iotop的本質是一個python腳本，從proc中獲取thread的IO信息，進行匯總。

 從下圖可以看出大部分的IO來源都來自于mysqld進程。因此可以確定dfa的負載來源是數據庫

Step3 pt-ioprofile定位負載來源文件

 pt-ioprofile的原理是對某個pid附加一個strace進程進行IO分析。

以下是摘自官網的一段警示：

 However, it works by attaching strace to the process using ptrace(), which will make it run very slowly until strace detaches. In addition to freezing the server, there is also some risk of the process crashing or performing badly after strace detaches from it, or indeed of strace not detaching cleanly and leaving the process in a sleeping state. As a result, this should be considered an intrusive tool, and should not be used on production servers unless you are comfortable with that.

通過ps aux|grep mysqld 找到 mysqld進程對應的進程號，通過pt-ioprofile查看哪個文件的IO占用時間最多。

默認參數下該工具展示的是IO占用的時間。

pt-ioprofile --profile-pid 3082

對于定位問題更有用的是通過IO的吞吐量來進行定位。使用參數 --cell=sizes，該參數將結果已 B/s 的方式展示出來

pt-ioprofile --profile-pid 3082 --cell

第一種：表未建主鍵和二級索引（排查最容易忽略的情況）

如下圖，當sql_thread在重放relay log時會根據表是否有主鍵（注：這就是為什么建表必須要有主鍵原因之一）和二級索引來判斷是否全表掃描

在MySQL5.6中提供了一個新的參數：slave_rows_search_algorithms, 可以部分解決無主鍵表導致的復制延遲問題，其基本思路是對于在一個ROWS EVENT中的所有前鏡像收集起來，然后在一次掃描全表時，判斷HASH中的每一條記錄進行更新；

slave_rows_search_algorithms由三個值的組合組成：TABLE_SCAN，INDEX_SCAN， HASH_SCANTABLE_SCAN,INDEX_SCAN  (默認配置，表示如果有索引就用索引，否則使用全表掃描)

使用組合包括：

INDEX_SCAN,HASH_SCAN

TABLE_SCAN,HASH_SCAN

TABLE_SCAN,INDEX_SCAN,HASH_SCAN(等價于INDEX_SCAN, HASH_SCAN)

對于以上MySQL主從延遲的原因相關內容，大家還有什么不明白的地方嗎？或者想要了解更多相關，可以繼續關注我們的行業資訊板塊。