內容提綱
一、簡介
二、Update 生命週期
2。1 聯結器
2。2 分析器
2。3 最佳化器
2。4 執行器
2。4。1 事務執行階段
2。4。2 事務提交階段
2。4。3 假設事務 ROLLBACK
三、影響事務提交延遲的幾種情況
四、Update 更新慢的排查思路
五、常見問題
一
簡介
在開始排錯Update之前,我們需要知道
Update 在 MySQL 中的生命週期是什麼
,
MySQL 如何執行一個事務的
。理解了如何執行,才知道如何去排查故障。
二
Update 生命週期
Server 層階段
2.1
●
聯結器
客戶端發起一個 TCP 請求後,MySQL Server 端會負責通訊協議處理、執行緒處理、賬號認證、安全檢查。
2.2
●
分析器
MySQL Server 端對一個 SQL 請求進行詞法分析(識別 select、from),然後會對語法 進行分析判斷語法是否正確。
2.3
●
最佳化器
最佳化器會分析 SQL 語句,選擇合適的索引,根據預結果集判斷是否使用全表掃描。
2.4
●
執行器
InnoDB引擎層階段
2.4.1 事務執行階段
1)
請求進入 InnoDB 引擎後,首先判斷該事務涉及到的資料頁是否在 BP 中,不存在則會從磁碟中載入此事務涉及的資料頁到 BP 緩衝池中,並對相應的索引資料頁加鎖
思考
資料是如何從磁碟載入到 BP 中的?
BP 中的新老生代是如何交替及回收?
如何對相應資料加鎖?
解答:
透過 B+Tree 讀取到磁碟的索引頁載入到 BP 緩衝池中。
1、
透過 space id 和 page no 雜湊計算之後把索引頁載入到指定的 buffer pool instance 中。
2、
判斷 Free List 是否有空閒頁可用(innodb_buffer_pool_pages_free、innodb_buffer_pool_wait_free),沒有 則淘汰髒頁或 LRU List 的 old。
3、
將資料頁載入到Free List 中,然後載入到 LRU List 的 old 區的 midpoint(頭部)。
4、
透過二分查詢法,找該頁對應的記錄,試圖給該事物涉及到的行記錄加上排他鎖。
判斷該事物當前記錄的行鎖被其他事物佔用的話,需要進入鎖等待。
進入鎖等待後,同時判斷會不會由於自己的加入導致了死鎖。
檢測到沒有鎖等待和不會造成死鎖後,行記錄加上排他鎖。
2)
將修改前的資料寫入到 Undo 中,修改後將回滾針執行 Undo log 中修改前的行
思考
為什麼要寫Undo 日誌?
Undo 的儲存方式是什麼?
解答:
Undo log 一般是邏輯日誌,記錄每行記錄。有兩個作用:提供回滾和 MVCC。
事務因為某些原因需要回滾時,可以藉助 Undo 日誌進行回滾,保證事務的一致性 在事務的不同隔離級別需要透過Undo log 實現。
當讀取某一行加鎖的資料時,可以透過 Undo log 實現(比 如:RR 級別),事務不結束,Undo log 就不刪除。
Undo log 的儲存方式是用段(segment)記錄在表空間中:
InnoDB 儲存引擎對 Undo 採用段方式管理,rollack segment 稱為回滾段,每個回滾段有 1024 個 。
Undo log segment,5。6 之後可以透過 innodb_undo_logs 自定義多少個回滾段,預設 128 個。
Undo log 預設儲存在共享表空間中,開啟了 innodb_file_per_table 將存在獨立表空間中。
3)
寫 redo log buffer 在 BP 中對資料進行修改操作,並將修改後的值寫入到 redo log buffer 中等待非同步 sync到磁碟
思考
什麼時候寫入 redo log buffer?
commit 後 log buffer 如何落盤到 redo log?
日誌刷盤規則是什麼?
解答:
什麼時候寫入 redo log buffer:
1、
先透過狀態值 Innodb_log_waits 判斷 redo log buffer 是否夠用,不夠用就等待。
2、
在 BP 緩衝池的 LRU List 中 old 區的 midpont 中對改資料頁的行記錄的欄位值做更新操作。
3、
把修改之後的欄位值寫入到redo log buffer 中,並給 LSN 加上當前 redo log 寫入的長度(寫入長度為length 的 redo log,LSN 就會加上 length)。
4、
因為 redo group commit,事務所產生的 redo log buffer 可能會和其他事務一同 flush 並且 sync 到磁碟上。
5、
欄位值在 BP 緩衝池更新成功後,對應的資料頁就是髒頁。
什麼時候落盤到 redo log 中:
1、
每次會將 log buffer 中的日誌寫入到 log file(這裡指 os buffer),然後在呼叫系統的 fsync 操作進行落盤。
在 commit 之後,透過 innodb_flush_log_at_trx_commit 來決定什麼時候將 log buffer 刷盤。
2、
值為 1(預設為 1):
事務每次提交會寫入 log buffer——>然後寫入 os buffer——->呼叫系統 fsync 刷到 log file on disk。
3、
值為 0:
事務提交是先寫入 log buffer——>每秒寫入 os buffer 並呼叫 fsync 落盤(最多丟失 1s 資料)。
4、值為 2:
每次提交只寫入 os buffer,然後每秒呼叫 fsync()將 os buffer 的日誌寫入到 log file on disk(最多丟失 1s 資料)。
日誌刷盤規則:
預設情況下事務每次提交會刷盤,是因為 innodb_flush_log_at_trx_commit 的值為 1。
這只是 InnoDB 在有 commit 動作後才會將日誌刷盤,屬於 InnoDB 儲存引擎刷盤規則之一。
日誌刷盤的幾種規則 :
1、
發出 commit 動作之後。由 innodb_flush_log_at_trx_commit 控制。
2、
每秒刷一次,重新整理頻率由 innodb_flush_log_at_timeout 值決定,預設為 1,刷日誌頻率與 commit 動作無關。
3、
當 log buffer 中使用記憶體超過一半。
4、
當有 checkpoint 時,checkpoint(資料頁刷盤)在一定程度上代表刷盤時日誌所處的 LSN 位置。
Checkpoint 刷盤規則:
InnoDB 中,資料刷盤的規則只有一個:checkpoint,觸發 checkpoint 後,會將 BP 中髒資料和髒日誌頁都重新整理到磁碟。
Checkpoint 分為兩種:
Sharp checkpoint:
在重用 redo log 檔案時(切割日誌),將所有記錄到 redo log 中對應的髒資料重新整理到磁碟。
Fuzzy checkpoint:
一次重新整理一小部分日誌到磁碟,並非所有髒日誌。
1、
master thread checkpoint:
master 執行緒每秒或每 10 秒刷一定比例髒頁到磁碟。
2、
Flush_lru_list checkpoint
:
5。6 之後透過 innodb_page_cleaners 變數指定 page cleaner 執行緒個數。
3、
Async/sync/ flush checkpoint
,同步刷盤還是非同步刷盤。
4、
Dirty page too much checkpoint :
髒頁怠惰強制觸發檢查點,保證快取空間空閒,由變數innodb_max_dirty_pages_pct 控制。
4)
寫 binlog cache
同時將修改的資訊按照 event 格式記錄到 binlog_cache 中,等待落盤。
如果 binlog cache 不夠用時,會寫入到 binlog 臨時檔案。
思考
事務 binlog event 的寫入流程是什麼?
解答:
一旦有事務提交,binlog cache 和 binlog 臨時檔案都會釋放(已經寫入 binlog file) 同一事務包含多個 DML 會共用同一個 binlog cache 和 binlog 臨時檔案。
1、事務開啟。
2、執行 dml 語句,dml 語句第一次執行時會分配 binlog cache。
3、執行 dml 語句期間生成的 event 不斷寫入 binlog cache。
4、binlog cache 滿了事務還沒執行完,會將 binlog cache 中的資料寫入到 binlog 臨時檔案同時清空 binlog cache,臨時檔案大小大於 max_binlog_cache_size 則報 error 1197。
5、事務提交,整個 binlog cache 和 binlog 臨時檔案資料全部寫入 binlog file,釋放 binlog cache(IO_CACHE) 和 binlog 臨時檔案 binlog 臨時檔案大小為 0,保留檔案描述符。
6、斷開連線,釋放 IO_CACHE。
5)
寫 change buffer
如果這個事務需要在二級索引上做修改,寫入到 change buffer page 中,等待之後,事務需要讀取該二級索引時進行 merge。
思考
什麼時候會用到 change buffer?
為什麼僅適用於普通索引頁?
哪些場景會觸發重新整理 change buffer?
什麼業務不適合/適合開啟 change buffer?
change buffer 相關引數有哪些?
解答:
什麼時候會用到 change buffer。
MySQL 5。5 之前叫 insert buffer,只針對 insert,之後叫 change buffer 對 delete 和 Update 也有效。
在對普通索引資料頁不在 BP 中,對頁進行寫操作,不會將磁碟資料載入到緩衝池中,僅僅記錄緩衝變更(可以理解為只記錄操作變更,不做真實資料操作)。
等待資料被讀取時,將資料 merge 到 BP 中,目的是降低寫操作磁碟 IO,提高效能。
為什麼僅適用於普通索引頁?
唯一索引或主鍵索引每次修改操作時,InnoDB 必須進行唯一性檢查。
即使索引頁不在緩衝池,也會去讀取磁碟頁,一次隨機 IO(透過 B+tree 查詢資料頁),一次順序 IO(寫 redo log)避免不了。
哪些場景會觸發重新整理 change buffer:
1、
資料頁被訪問。
2、
master thread 每隔 10s 會進行操作。
3、
資料庫 BP 不夠用時。
4、
資料庫正常關閉時。
5、
redo log 寫滿時(幾乎不會出現,redo log 被寫滿資料庫處於無法寫入狀態)。
什麼業務不適合/適合開啟 change buffer:
不適合:
1、資料庫都是唯一索引。
2、寫入一個數據後,立刻讀取上述場景,在寫操作之後,本來就要進入 BP 中,此時 change buffer 反而成了累贅。
適合:
1、資料庫大部分是非唯一索引。
2、業務是寫多讀少,或者寫後不是立刻讀。
3、讀寫分離下主庫可以使用。
可以使用 change buffer,減少一次隨機 IO,最佳化定期批次寫磁碟。
change buffer 相關引數有哪些:
show global variables like ‘%innodb_change_buffer%’;
innodb_change_buffer_max_size;
配置寫緩衝的大小,佔整個緩衝池的比例,預設值是 25%,最大值是 50%(寫多讀少才需調大,讀多寫少 25%就夠)。
innodb_change_buffering;
配置那些寫操作啟用寫緩衝,可以設定成 all/none/inserts/deletes 等。
2.4.2 事務提交階段
開啟 binlog 選項之後,執行事務提交會進入二階段提交模式(prepare 階段和 commit 階段。
兩階段涉及兩個引數(sync_binlog和innodb_flush_log_at_trx_commit)。
1)
事務提交分為 prepare 階段與 commit 階段(兩階段提交)
事務的 commit 操作在儲存引擎和 server 層採用內部 XA。
兩階段提交協議保證事務的一致性,主要保證 redo log 和 binlog 的原子性。
2)
Redo log prepare
寫入 redo log 處於 prepare 階段,並且寫入事務的 xid。
將redo log buffer重新整理到redo log磁碟檔案中,用於崩潰恢復。
刷盤的方式由innodb_flush_log_at_trx_commit 決定。
3)
Binlog write&fync: 執行器把 binlog cache 裡的完整事務和 redo log prepare 中的 xid 寫入到 binlog 中
Dump 執行緒會從 binlog cache 裡把 event 主動傳送給 slave 的 I/O 執行緒,同時執行 fsync 刷盤(大事務的話比 較耗時)並清空 binlog cache。
Binlog 刷盤的方式由 sync_binlog 決定。binlog 寫入完成,事務就算成功。
總結:
事務執行過程中,先把日誌寫到 binlog cache,事務提交的時候,再把 binlog cache 寫入到 binlog file 中。
當 sync_binlog 為 1 時,binlog 落盤後才會通知 dump thread 進行主從複製。
4)
Redo log commit commit
提交階段中,該事務產生的 redo log 已經 sync 到磁碟中,在 redo log 裡標記 commit,說明事務提交成功。
5)
事務提交,釋放行記錄持有的排它鎖
6)
Binlog 和 redo log 落盤後觸發重新整理髒頁操作
先把該髒頁複製到 doublewrite buffer 裡,再把 dobulewrite buffer 裡的資料,重新整理到共享表空間(ibdata),然後髒頁重新整理到磁碟中,此時記憶體頁和資料頁一致。
思考
BP 中的髒頁刷盤機制是什麼?
解答:
當 InnoDB 中髒頁比例超過 innodh_max_dirty_pages_pct_lwm 的值時,開始刷盤。
2.4.3 假設事務 ROLLBACK
因為系統異常或顯示回滾,所有資料變更會變成原來的,透過回滾日誌中資料進行恢復。
對於 in-place(原地)更新
,將資料回滾到最老版本;
對於 delete+insert 方式
,標記刪除的記錄清理刪除標記,同時把插入的聚集索引和二級索引記錄也會刪除。
三
影響事務提交延遲的幾種情況
在事務執行階段:
3.1
●
鎖等待
(1)
RR 模式下 insert 鎖等待 gap lock 鎖等待導致;
(2)
Insert 等待 MDL 鎖導致;
(3)
Table lock。
3.2
●
IO 方面
(1)
慢 sql 導致 io 高;
(2)
其他程式佔用比價高;
(3)
BP 命中率比較低;
(4)
併發導致;
(5)
innodb buffer pool 不夠用;
(6)
Update、delete 更新資料行數大(>W)。
3.3
●
Buffer 方面
(1)
redo log buffer 是否夠用透過 Innodb_log_waits 確認。
(2)
Redo log buffer 刷盤方式透過 innodb_flush_log_at_trx_commit。
(3)
Binlog cache 是否夠用,建立臨時檔案、消耗 IO。
(4)
Change buffer 是否夠用。
3.4
●
落盤延遲
(1)sync_binlog 引數。
(2)binlog_group_commit_sync_delay 引數。
(3)innodb_flush_commit 引數。
(4)檢視 innodb_buffer_pool 的命中率,檢視髒頁重新整理頻率效果。
四
Update 更新慢的排查思路
排查思路:
4.1
●
排查例項系統性能情況
(IO、CPU、memory),排除性能干擾
如果 CPU 高、IO 高、wa 大:
先排查慢 SQL,再查當前併發數,一般是大量併發慢 SQL 導致;
如果 CPU 高、IO 中、wa 小:
排查慢 SQL,在檢視當前併發數,一般是單個計算 SQL 導致;
如果 CPU 低、IO 高、wa 低:
排查當前佔用 io 高的執行緒,有可能是 page clean 導致或日誌重新整理頻繁導致。
4.2
●
檢查 MySQL狀態
檢視 mysql porcesslist,檢視當前是否有 wait lock(表鎖,行鎖,meata lock 等);
檢視 mysql processlist,是否有大量 send data、init、commit、clean up 狀態;
檢視 mysql processlist,計算併發,排查是否有併發壓力;
檢視 innodb buffer pool 命中率,排查 buffer 是否夠用;
檢視 mysql tmp,是否夠用,open tables 是否等於 table_open_cache。
4.3
●
分析 SQL 語句
透過 explain 分析 SQL 的執行情況,是否走索引,是否存在 union;
透過 explain 分析 SQL 的執行情況,是否存在大表驅動小表,多表 join;
檢查 SQL 是否存在產生額外臨時表;
使用 profile 分析單條 SQL 語句。
4.4
●
分析應用程式執行 SQL 慢的時間
觀察是單個 SQL 執行慢,還是所有語句都慢;
慢的 SQL 的時間是否有規律,有助於排查 MysSQL 的相關引數。
4.5
●
抓包及 strace 分析
使用 tcpdump 進行抓包,分析是 MySQL 返回慢,還是網路慢;
使用 strace 分析 MySQL 內部哪裡慢,哪個函式導致的。
五
常見問題
1、
Update 全表更新一個欄位,資料量為 10w,更新特別慢;
2、
Update 引起死鎖問題;
3、
Update 幾百條資料消耗了 10s;
4、
Update 同一個表,有些更新快,有些更新慢。
NEWS
Update的問題還不止於此,透過閱讀本篇文章,相信您對如何發現、排查、解決Update可能引發的問題,有了更進一步的認識。
在本小節中,列舉了部分常見的案例,希望對您學習有所幫助。此外,也推薦您自己動手搭建測試環境,發現新的問題。
Enjoy GreatSQL :)