mysql兩個重要的日誌redolog和binlog

来源:https://www.cnblogs.com/eternityz/archive/2020/03/08/12443016.html
-Advertisement-
Play Games

一.redo log 使用原因 原理 二.binlog(歸檔日誌) 與redo log的區別 三.兩段提交 更新過程 update T set c=c+1 where ID=2; 1. 執行器先找引擎取 ID=2 這一行。ID 是主鍵，引擎直接用樹搜索找到這一行。如果 ID=2 這一行所在的數據頁本 ...


一.redo log

  • 使用原因

    在 MySQL 里有這個問題，如果每一次的更新操作都需要寫進磁碟，然後磁碟也要找到對應的那條記錄，然後再更新，整個過程 IO 成本、查找成本都很高

其實就是 MySQL 里經常說到的 WAL 技術，WAL 的全稱 是 Write-Ahead Logging，它的關鍵點就是先寫日誌，再寫磁碟

當有一條記錄需要更新的時候，InnoDB 引擎就會先把記錄寫到 redo log裡面，並更新記憶體，這個時候更新就算完成了。同時，InnoDB 引擎會在適當的時候，將這個操作記錄更新到磁碟裡面，而這個更新往往是在系統比較空閑的時候做

  • 原理

    InnoDB 的 redo log 是固定大小的，比如可以配置為一組 4 個文件，每個文 件的大小是 1GB，那麼這塊“粉板”總共就可以記錄 4GB 的操作。從頭開始寫，寫到末 尾就又回到開頭迴圈寫，如上面這個圖所示。

write pos 是當前記錄的位置，一邊寫一邊後移，寫到第 3 號文件末尾後就回到 0 號文件 開頭。checkpoint 是當前要擦除的位置，也是往後推移並且迴圈的，擦除記錄前要把記錄 更新到數據文件。

write pos 和 checkpoint 之間的是“粉板”上還空著的部分，可以用來記錄新的操作。如 果 write pos 追上 checkpoint，表示“粉板”滿了，這時候不能再執行新的更新，得停下 來先擦掉一些記錄，把 checkpoint 推進一下。

有了 redo log，InnoDB 就可以保證即使資料庫發生異常重啟，之前提交的記錄都不會丟 失，這個能力稱為crash-safe。

二.binlog(歸檔日誌)

  • 與redo log的區別

    1. redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 層實現的，所有引擎 都可以使用。

2. redo log 是物理日誌，記錄的是“在某個數據頁上做了什麼修改”,雖然沒有保存整頁數據，但是可以記錄頁面級別的變更。;binlog 是邏輯日 志，記錄的是這個語句的原始邏輯，比如“給 ID=2 這一行的 c 欄位加 1 ”。

3. redo log 是迴圈寫的，空間固定會用完;binlog 是可以追加寫入的。“追加寫”是指 binlog 文件寫到一定大小後會切換到下一個，並不會覆蓋以前的日誌。

三.兩段提交

更新過程

  • update T set c=c+1 where ID=2;
  1. 執行器先找引擎取 ID=2 這一行。ID 是主鍵，引擎直接用樹搜索找到這一行。如果 ID=2 這一行所在的數據頁本來就在記憶體中，就直接返回給執行器;否則，需要先從磁 盤讀入記憶體，然後再返回。
  2. 執行器拿到引擎給的行數據，把這個值加上 1，比如原來是 N，現在就是 N+1，得到 新的一行數據，再調用引擎介面寫入這行新數據。
  3. 引擎將這行新數據更新到記憶體中，同時將這個更新操作記錄到 redo log 裡面，此時 redo log 處於 prepare 狀態(處於prepare狀態之後要寫入磁碟中,但是redo log的commit得標識為沒有commit)。然後告知執行器執行完成了，隨時可以提交事務。
  4. 執行器生成這個操作的 binlog，並把 binlog 寫入磁碟。
  5. 執行器調用引擎的提交事務介面，引擎把剛剛寫入的 redo log 改成提交(commit)狀態，更新完成。

  • 淺色框表示是在 InnoDB 內部執行的， 深色框表示是在執行器中執行的。

  • 必要性

    如果不使用“兩階段提交”，那麼資料庫的狀態就有可能和用它的日誌恢復出來的庫的狀態不一致。

  • 兩段提交發生時機

    我們單獨寫一個update語句的時候，就預設提交事務的，我們的兩階段，是發生在“提交階段”的
如果是有begin...commit的語句序列，在執行“commit”這個語句的時候發生的提交階段,兩階段也就在此時發生
  • 在兩階段提交的不同時刻，MySQL 異常重啟會出現什麼現象。

    • 如果在圖中時刻 A 的地方，也就是寫入 redo log 處於 prepare 階段之後、寫 binlog 之 前，發生了崩潰(crash)，由於此時 binlog 還沒寫，redo log 也還沒提交，所以崩潰恢復的時候，這個事務會回滾。這時候，binlog 還沒寫，所以也不會傳到備庫。

    • 在時刻 B，也就是 binlog 寫完，redo log 還沒 commit 前發生 crash，那崩潰恢復的時候 MySQL 會怎麼處理 ?

      1. 如果 redo log 裡面的事務是完整的，也就是已經有了 commit 標識，則直接提交;
2. 如果 redo log 裡面的事務只有完整的 prepare，則判斷對應的事務 binlog 是否存在並完整:
        a. 如果是，則提交事務; 
        b. 否則，回滾事務。

協助理解:
當時刻B發生crash，重啟後這部分redo log都丟失了，那麼何談判斷redo log是否有完整的prepare還是commit標誌呢？
答: 寫binlog之前，就已經都寫了盤並且fsync同步到磁碟了

    • 時刻 B 發生 crash 對應的就是 2(a) 的情況，崩潰恢復過程中事務會被提交。

四.binlog寫入機制

其實，binlog 的寫入邏輯比較簡單:事務執行過程中，先把日誌寫到 binlog cache，事 務提交的時候，再把 binlog cache 寫到 binlog 文件中。

一個事務的 binlog 是不能被拆開的，因此不論這個事務多大，也要確保一次性寫入。這 就涉及到了 binlog cache 的保存問題。

系統給 binlog cache 分配了一片記憶體，每個線程一個，參數 binlog_cache_size 用於控 制單個線程內 binlog cache 所占記憶體的大小。如果超過了這個參數規定的大小，就要暫 存到磁碟。

事務提交的時候，執行器把 binlog cache 里的完整事務寫入到 binlog 中，並清空 binlog cache。狀態如圖 1 所示。

可以看到，每個線程有自己 binlog cache，但是共用同一份 binlog 文件。

圖中的 write，指的就是指把日誌寫入到文件系統的 page cache，並沒有把數據持久化 到磁碟，所以速度比較快。此時write步驟,寫入操作系統維護的記憶體中,此記憶體是磁碟中文件系統申請的記憶體;

圖中的 fsync，才是將數據持久化到磁碟的操作。一般情況下，我們認為 fsync 才占磁 盤的 IOPS。

write 和 fsync 的時機，是由參數 sync_binlog 控制的:

  • sync_binlog=0 的時候，表示每次提交事務都只 write，不 fsync;
  • sync_binlog=1 的時候，表示每次提交事務都會執行 fsync;
  • sync_binlog=N(N>1) 的時候，表示每次提交事務都 write，但累積 N 個事務後才fsync。

因此，在出現 IO 瓶頸的場景里，將 sync_binlog 設置成一個比較大的值，可以提升性 能。在實際的業務場景中，考慮到丟失日誌量的可控性，一般不建議將這個參數設成 0， 比較常見的是將其設置為 100~1000 中的某個數值。

但是，將 sync_binlog 設置為 N，對應的風險是:如果主機發生異常重啟，會丟失最近 N 個事務的 binlog 日誌。

五.redo log寫入機制

redo log buffer 裡面的內容，是不是每次生成後都要直接持久化到磁 盤呢?

答案是，不需要。 如果事務執行期間 MySQL 發生異常重啟，那這部分日誌就丟了。由於事務並沒有提交， 所以這時日誌丟了也不會有損失。

那麼，另外一個問題是，事務還沒提交的時候，redo log buffer 中的部分日誌有沒有可能 被持久化到磁碟呢?

答案是，確實會有, 下麵解釋。

1.寫入狀態

這個問題，要從 redo log 可能存在的三種狀態說起。這三種狀態，對應的就是圖 2 中的 三個顏色塊。

這三種狀態分別是:

  1. 存在redo log buffer中,物理上是存在mysql進程記憶體中,就是圖中紅色部分
  2. 寫到磁碟(write),但是沒有持久化(fsync),物理上是在文件系統的page cache裡面,也就是圖中得黃色部分
  3. 持久化到磁碟,對應的是hard disk,也就是圖中得綠色部分

日誌寫到 redo log buffer 是很快的，wirte 到 page cache 也差不多，但是持久化到磁碟 的速度就慢多了。

2.寫入策略

為了控制 redo log 的寫入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit 參 數，它有三種可能取值:

  1. 設置為 0 的時候，表示每次事務提交時都只是把 redo log 留在 redo log buffer 中 ;
  2. 設置為 1 的時候，表示每次事務提交時都將 redo log 直接持久化到磁碟;
  3. 設置為 2 的時候，表示每次事務提交時都只是把 redo log 寫到 page cache。

InnoDB 有一個後臺線程，每隔 1 秒，就會把 redo log buffer 中的日誌，調用 write 寫 到文件系統的 page cache，然後調用 fsync 持久化到磁碟。

註意，事務執行中間過程的 redo log 也是直接寫在 redo log buffer 中的，這些 redo log 也會被後臺線程一起持久化到磁碟。也就是說，一個沒有提交的事務的 redo log，也 是可能已經持久化到磁碟的。

實際上，除了後臺線程每秒一次的輪詢操作外，還有兩種場景會讓一個沒有提交的事務的 redo log 寫入到磁碟中。

  1. 一種是，redo log buffer 占用的空間即將達到 innodb_log_buffer_size 一半的時 候，後臺線程會主動寫盤。註意，由於這個事務並沒有提交，所以這個寫盤動作只是 write，而沒有調用 fsync，也就是只留在了文件系統的 page cache。
  2. 另一種是，並行的事務提交的時候，順帶將這個事務的 redo log buffer 持久化到磁 盤。假設一個事務 A 執行到一半，已經寫了一些 redo log 到 buffer 中，這時候有另 外一個線程的事務 B 提交，如果 innodb_flush_log_at_trx_commit 設置的是 1，那麼 按照這個參數的邏輯，事務 B 要把 redo log buffer 里的日誌全部持久化到磁碟。這時 候，就會帶上事務 A 在 redo log buffer 里的日誌一起持久化到磁碟。

3.兩階段提交詳解

這裡需要說明的是，我們介紹兩階段提交的時候說過，時序上 redo log 先 prepare， 再 寫 binlog，最後再把 redo log commit。

如果把 innodb_flush_log_at_trx_commit 設置成 1，那麼 redo log 在 prepare 階段就 要持久化一次，因為有一個崩潰恢復邏輯是要依賴於 prepare 的 redo log，再加上 binlog 來恢復的。

每秒一次後臺輪詢刷盤，再加上崩潰恢復這個邏輯，InnoDB 就認為 redo log 在 commit 的時候就不需要 fsync 了，只會 write 到文件系統的 page cache 中就夠了。

通常我們說 MySQL 的“雙 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都設置成 1。也就是說，一個事務完整提交前，需要等待兩次刷盤，一次是 redo log(prepare 階段)，一次是 binlog。

在“雙 1”配置下,兩階段提交的詳細流程

  1. redo log的prepare write階段,寫入到page cache 里(redo log prepare)
  2. 然後binlog進入write階段,寫入page cache里(binlog)
  3. 然後redo log 的prepare階段,進行持久化fsync操作,持久化到磁碟(redo log prepare)
  4. binlog進行持久化階段fsync階段,持久化到磁碟(binlog)
  5. commit階段,先將binlog添加commit標識,再將redo log添加commit標識,redo log進行write,寫入page cache(commit)

六.組提交

1.redo log組提交

這裡，我需要先和你介紹日誌邏輯序列號(log sequence number，LSN)的概念。LSN 是單調遞增的，用來對應 redo log 的一個個寫入點。每次寫入長度為 length 的 redo log， LSN 的值就會加上 length。

LSN 也會寫到 InnoDB 的數據頁中，來確保數據頁不會被多次執行重覆的 redo log。關 於 LSN 和 redo log、checkpoint 的關係，我會在後面的文章中詳細展開。

如圖 3 所示，是三個併發事務 (trx1, trx2, trx3) 在 prepare 階段，都寫完 redo log buffer，持久化到磁碟的過程，對應的 LSN 分別是 50、120 和 160。

從圖中可以看到，

  1. trx1 是第一個到達的，會被選為這組的 leader;
  2. 等 trx1 要開始寫盤的時候，這個組裡面已經有了三個事務，這時候 LSN 也變成了160;
  3. trx1 去寫盤的時候，帶的就是 LSN=160，因此等 trx1 返回時，所有 LSN 小於等於160 的 redo log，都已經被持久化到磁碟;
  4. 這時候 trx2 和 trx3 就可以直接返回了。

所以，一次組提交裡面，組員越多，節約磁碟 IOPS 的效果越好。但如果只有單線程壓 測，那就只能老老實實地一個事務對應一次持久化操作了。

在併發更新場景下，第一個事務寫完 redo log buffer 以後，接下來這個 fsync 越晚調 用，組員可能越多，節約 IOPS 的效果就越好。

2.binlog組提交

為了讓一次 fsync 帶的組員更多，MySQL 有一個很有趣的優化:拖時間。在介紹兩階段 提交的時候，我曾經給你畫了一個圖，現在我把它截過來。

圖中，我把“寫 binlog”當成一個動作。但實際上，寫 binlog 是分成兩步的:

  1. 先把 binlog 從 binlog cache 中寫到磁碟上的 binlog 文件,此時write步驟,寫入操作系統維護的記憶體中,此記憶體是磁碟中文件系統申請的記憶體;
  2. 調用 fsync 持久化。

MySQL 為了讓組提交的效果更好，把 redo log 做 fsync 的時間拖到了步驟 1 之後。也 就是說，上面的圖變成了這樣:

這麼一來，binlog 也可以組提交了。在執行圖 5 中第 4 步把 binlog fsync 到磁碟時，如 果有多個事務的 binlog 已經寫完了，也是一起持久化的，這樣也可以減少 IOPS 的消耗。

不過通常情況下第 3 步執行得會很快，所以 binlog 的 write 和 fsync 間的間隔時間短， 導致能集合到一起持久化的 binlog 比較少，因此 binlog 的組提交的效果通常不如 redo log 的效果那麼好。

如果你想提升 binlog 組提交的效果，可以通過設置 binlog_group_commit_sync_delay 和binlog_group_commit_sync_no_delay_count 來實現。

  1. binlog_group_commit_sync_delay 參數，表示延遲多少微秒後才調用 fsync;

  2. binlog_group_commit_sync_no_delay_count 參數，表示累積多少次以後才調用fsync。

這兩個條件是或的關係，也就是說只要有一個滿足條件就會調用 fsync。

所以，當 binlog_group_commit_sync_delay 設置為 0 的時候， binlog_group_commit_sync_no_delay_count 也無效了。

之前有同學在評論區問到，WAL 機制是減少磁碟寫，可是每次提交事務都要寫 redo log 和 binlog，這磁碟讀寫次數也沒變少呀?

現在你就能理解了，WAL 機制主要得益於兩個方面:

  1. redo log 和 binlog 都是順序寫，磁碟的順序寫比隨機寫速度要快;
  2. 組提交機制，可以大幅度降低磁碟的 IOPS 消耗。

七.mysql提升IO性能

可以考慮以下三種方法:

  1. 設置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 參數，減少 binlog 的寫盤次數。這個 方法是基於“額外的故意等待”來實現的，因此可能會增加語句的響應時間，但沒有丟失數據的風險。
  2. 將 sync_binlog 設置為大於 1 的值(比較常見是 100~1000)。這樣做的風險是，主 機掉電時會丟 binlog日誌。
  3. 將 innodb_flush_log_at_trx_commit 設置為 2。這樣做的風險是，主機掉電的時候會丟數據。

我不建議你把 innodb_flush_log_at_trx_commit 設置成 0。因為把這個參數設置成 0， 表示 redo log 只保存在沒有生氣啦申請的記憶體中，這樣的話 MySQL 本身異常重啟也會丟數據，風險太 大。而 redo log 寫到文件系統的 page cache 的速度也是很快的，所以將這個參數設置 成 2 跟設置成 0 其實性能差不多，但這樣做 MySQL 異常重啟時就不會丟數據了(因為數據已經寫入了磁碟中文件系統申請的記憶體中是操作系統維護的記憶體,而不是在mysql申請的記憶體中)，相比之下風險會更小。

站在巨人的肩膀上摘蘋果:

https://time.geekbang.org/column/intro/100020801


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • K8s深入瞭解
    Ingress控制器介紹 安裝部署traefik 創建traefik的web ui的ingress規則 ingress實驗 hostPath實驗 PV和PVC 研究的方向 重啟k8s二進位安裝(kubeadm)需要重啟組件 ...
  • K8s初識
    k8s系統架構 從系統架構來看，k8s分為2個節點 Master 控制節點 指揮官 Node 工作節點 幹活的 1.Master節點組成 API Server ：提供k8s API介面 主要處理Rest操作以及更新Etcd中的對象 是所有資源增刪改查的唯一入口。 Scheduler：資源調度器 根據 ...
  • ELK日誌收集
    ELk簡介 基本架構圖 傳統日誌分析需求(面試) 1.找出訪問網站頻次最高的 IP 排名前十 2.找出訪問網站排名前十的 URL 3.找出中午 10 點到 2 點之間 www 網站訪問頻次最高的 IP 4.對比昨天這個時間段和今天這個時間段訪問頻次有什麼變化 5.對比上周這個時間和今天這個時間的區別 ...
  • nbd 相關概念及操作
    周五(3月6號)筆者發現有台物理機上掛載了一個並非常用的nbd設備，估計是之前人做的測試留下來的，決定卸載它，順帶瞭解了下nbd的設備信息。 什麼是nbd 全稱是network block device，類似於nfs，遠程設備可以掛載，只不過掛載的不再是文件系統，而是塊設備。 nbd一般分為clie ...
  • Could not find archived log for sequence 587 thread 1 under default destinations SQL
    最近遇到這個問題，其實提示很簡單了。 去oracle 論壇看下，有人回覆了 Extract was off for two long. All redo logs have been switched. You have to provide archive logs to Oracle defau ...
  • ElasticSearch 分詞器，瞭解一下
    這篇文章主要來介紹下什麼是 Analysis ，什麼是分詞器，以及 ElasticSearch 自帶的分詞器是怎麼工作的，最後會介紹下中文分詞是怎麼做的。 首先來說下什麼是 Analysis： 什麼是 Analysis？ 顧名思義，文本分析就是 把全文本轉換成一系列單詞（term/token）的過程 ...
  • MySQL中的函數索引（Generated Column）及一次SQL優化
    MySQL 中是沒有 Oracle 的函數索引功能的，把 MySQL 的 Generated Column 稱為“函數索引”並不准確，但可以和函數索引達到同樣的效果，也有人把這個特性稱為“衍生列”。 Generated Column 是什麼 Generated Column 的值是根據其定義的表達式 ...
  • mysql中的事務隔離級別及可重覆讀讀提交詳細分析(mvcc多版本控制/undo log)
    一.事物隔離級別 讀未提交(read uncommitted)是指，一個事務還沒提交時，它做的變更就能被別的事務看到.通俗理解,別人改數據的事務尚未提交，我在我的事務中也能讀到。 讀提交(read committed)是指，一個事務提交之後，它做的變更才會被其他事務看到。通俗理解,別人改數據的事務已 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類