[[422164]]

本文轉(zhuǎn)載自微信公眾號(hào)「數(shù)據(jù)和云」，作者崔虎龍。轉(zhuǎn)載本文請(qǐng)聯(lián)系數(shù)據(jù)和云公眾號(hào)。

MySQL為了保證數(shù)據(jù)會(huì)做很多checkpoint動(dòng)作。特別是InnoDB采用Write Ahead Log策略來防止宕機(jī)導(dǎo)致的數(shù)據(jù)丟失：即事務(wù)提交時(shí)，先寫重做日志，再修改內(nèi)存數(shù)據(jù)頁的方式臟數(shù)據(jù)刷新等。除此之外，還有服務(wù)重新啟動(dòng)。

一.checkpoint介紹

checkpoint是為了解決哪些問題呢?

• 對(duì)于數(shù)據(jù)需要頻繁更新的場景，要實(shí)時(shí)更新，對(duì)于MySQL來說，只處理IO，就能把性能耗盡。
• Redo日志大小也是有限的，通過刷新策略，可以更有效的重復(fù)使用文件，不需要開辟新的空間。
• 緩沖區(qū)大小有限。數(shù)據(jù)不刷到硬盤，對(duì)于查詢業(yè)務(wù)，命中率越來越小。
• 數(shù)據(jù)庫宕機(jī)，崩潰恢復(fù)期間，需要從上次的檢查點(diǎn)進(jìn)行恢復(fù)，使得效率提升。
• 物理備份日志點(diǎn)。

InnoDB引擎通過LSN(Log Sequence Number)來標(biāo)記版本，LSN是日志空間中每條日志的結(jié)束點(diǎn)，用字節(jié)偏移量來表示。每個(gè)Page有LSN，每個(gè)Redo log有LSN，每個(gè)checkpoint也有LSN。

checkpoint會(huì)對(duì)哪些MySQL實(shí)體做操作?

• Dirty page：InnoDB緩沖池中已經(jīng)在內(nèi)存中更新的頁面，其中的更改還沒有寫入(刷新)到數(shù)據(jù)文件。
• Flush：將已緩沖在內(nèi)存區(qū)域或臨時(shí)磁盤存儲(chǔ)區(qū)域中的數(shù)據(jù)庫文件的更改寫入。
• Redo log：數(shù)據(jù)更改信息記錄文件。

二.checkpoint機(jī)制

從官方提供的說明中checkpoint分為兩個(gè)：

• Fuzzy checkpoint：進(jìn)行部分臟頁的刷新，有效循環(huán)利用Redo日志。
• Sharp checkpoint：發(fā)生在關(guān)閉數(shù)據(jù)庫時(shí)，將所有臟頁刷回磁盤。

通過以上兩個(gè)方式，在不同的情況下觸發(fā)checkpoint：

1) flush_lru_list

flush_lru_list checkpoint是在單獨(dú)的page cleaner線程中執(zhí)行的。Buffer Pool的LRU空閑列表中保留一定數(shù)量的空閑頁面，來保證Buffer Pool中有足夠的空間應(yīng)對(duì)新的數(shù)據(jù)庫請(qǐng)求。

在空閑列表不足時(shí)，發(fā)生flush_lru_list checkpoint，空閑數(shù)量閾值是可以配置的。

如innodb_page_cleaners線程的數(shù)量超過了緩沖池實(shí)例(innodb_buffer_pool_instances)的數(shù)量，則innodb_page_cleaners將自動(dòng)設(shè)置為與innodb_buffer_pool_instances相同的值。

2)Dirty Page

臟頁數(shù)量太多時(shí)，InnoDB引擎會(huì)強(qiáng)制進(jìn)行checkpoint，下面有幾個(gè)核心參數(shù)控制的checkpoint點(diǎn)。

• 刷新比率

innodb_max_dirty_pages_pct_lwm閾值的目的是控制緩沖池中臟頁的百分比，防止臟頁的數(shù)量達(dá)到innodb_max_dirty_pages_pct變量定義的閾值(默認(rèn)值為90)。當(dāng)緩沖池中的臟頁百分比達(dá)到閾值時(shí)，InnoDB會(huì)主動(dòng)刷新緩沖池中的頁。

• innodb_max_dirty_pages_pct：

InnoDB會(huì)嘗試從緩沖池中刷新數(shù)據(jù)，這樣臟頁的百分比就不會(huì)超過這個(gè)值。innodb_max_dirty_pages_pct默認(rèn)90%。

• innodb_max_dirty_pages_pct_lwm：

定義低水位標(biāo)記，表示啟用預(yù)沖洗以控制臟頁比率的臟頁百分比。0值將完全禁用預(yù)刷新行為。配置的值應(yīng)該總是低于innodb_max_dirty_pages_pct的值。

• innodb_flush_neighbors

變量定義了從緩沖池中刷新一個(gè)頁是否也會(huì)刷新相同范圍內(nèi)的其他臟頁。

默認(rèn)設(shè)置0禁用innodb_flush_neighbors。

設(shè)置為1將刷新同一區(qū)段中的連續(xù)臟頁。

設(shè)置為2將刷新同一區(qū)段中的臟頁。

當(dāng)表數(shù)據(jù)存儲(chǔ)在傳統(tǒng)的HDD存儲(chǔ)設(shè)備上時(shí)，與在不同時(shí)間刷新單個(gè)頁相比，在一次操作中刷新相鄰頁可以減少I/O開銷(主要用于磁盤尋道操作)。對(duì)于SSD來說：普遍場景在IO方面的處理能力已經(jīng)非常優(yōu)秀?？梢源蜷_這個(gè)參數(shù)。

• innodb_lru_scan_depth

變量定義了對(duì)于每個(gè)緩沖池實(shí)例，緩沖池LRU列出的頁面清理器線程掃描的臟頁面的深度。這是一個(gè)由頁面page clear thread每秒執(zhí)行一次的后臺(tái)操作。

小于默認(rèn)值的設(shè)置通常適用于大多數(shù)工作負(fù)載，顯著高于必要值時(shí)可能會(huì)影響性能。只有在典型工作負(fù)載下有空閑I/O容量時(shí)，才考慮增加該值。相反，如果寫密集的工作負(fù)載使您的I/O容量飽和，則需要降低該值，特別是在大型緩沖池的情況下。

另外，在改變緩沖池實(shí)例數(shù)量時(shí)，考慮調(diào)整innodb_lru_scan_depth，因?yàn)閕nnodb_lru_scan_depth * innodb_buffer_pool_instances定義了page clear thread每秒執(zhí)行的工作量。

innodb_flush_neighbors和innodb_lru_scan_depth變量主要用于寫密集型的工作負(fù)載。對(duì)于大量DML活動(dòng)，如果刷新不夠激烈，則刷新可能會(huì)滯后;如果刷新太激烈，磁盤寫可能會(huì)使I/O容量飽和。

• innodb_io_capacity

設(shè)置適用于所有的緩沖池實(shí)例。當(dāng)刷新臟頁時(shí)，I/O容量將平均分配給緩沖池實(shí)例。

注意，如果刷新落后，緩沖池的刷新速率可能會(huì)超過InnoDB可用的I/O容量，這是由innodb_io_capacity設(shè)置定義的。innodb_io_capacity_max值定義了這種情況下的I/O容量上限，這樣I/O活動(dòng)的峰值不會(huì)占用服務(wù)器的整個(gè)I/O容量。一般設(shè)置有不同的硬盤類型配置 SAS 200~1000 ，SSD 2000~5000 ，PCI-E 10000-50000

3)Adaptive Flushing

當(dāng)產(chǎn)生大量寫密集型工作負(fù)載時(shí)，可能會(huì)導(dǎo)致吞吐量的突然變化。當(dāng)InnoDB Redo日志文件滿了，就會(huì)出現(xiàn)一個(gè)Sharp checkpoint，導(dǎo)致臨時(shí)的吞吐量降低。即使innodb_max_dirty_pages_pct閾值未達(dá)到，也會(huì)出現(xiàn)這種情況。

innodb_adaptive_flushing_lwm變量定義了Redo日志容量的低水位標(biāo)志。當(dāng)超過該閾值時(shí)，啟用自適應(yīng)刷新(Adaptive Flushing)。

innodb_flushing_avg_loops定義了InnoDB保持先前計(jì)算的刷新狀態(tài)快照的迭代次數(shù)，控制自適應(yīng)刷新對(duì)前臺(tái)工作負(fù)載變化的響應(yīng)速度。就是說控制統(tǒng)計(jì)前N個(gè)page flush速率，避免太快flush。

高的值意味著InnoDB保持先前計(jì)算的快照的時(shí)間更長，因此自適應(yīng)刷新響應(yīng)更慢。如日志空間利用率未達(dá)到75%，則應(yīng)該使用較高的innodb_flushing_avg_loops值來保持盡可能平滑的刷新。對(duì)于具有極端負(fù)載峰值或日志文件不提供大量空間的系統(tǒng)，應(yīng)使較小的值允許flush以密切跟蹤工作負(fù)載更改，并有助于避免達(dá)到75%的日志空間利用率。

4)限制空閑期間的緩沖區(qū)刷新

從MySQL 8.0.18開始，你可以使用innodb_idle_flush_pct變量來限制空閑時(shí)間段(數(shù)據(jù)庫頁面不被修改的時(shí)間段)的緩沖池刷新速率。innodb_idle_flush_pct的值是innodb_io_capacity設(shè)置的百分比，innodb_io_capacity定義了每秒可用于InnoDB的I/O操作次數(shù)。innodb_idle_flush_pct的默認(rèn)值是100，這是innodb_io_capacity設(shè)置的100%。為了限制空閑時(shí)間的刷新，定義一個(gè)innodb_idle_flush_pct小于100的值。

在空閑期間限制頁面刷新可以幫助延長固態(tài)存儲(chǔ)設(shè)備的壽命，但其的副作用可能包括在長時(shí)間的空閑期間之后更長的關(guān)閉時(shí)間以及在服務(wù)器發(fā)生故障時(shí)更長的恢復(fù)時(shí)間等問題。

5)Redo 日志

Redo日志在物理上表示為一組文件，通常命名為ib_logfile0和ib_logfile1。重做日志中的數(shù)據(jù)按照受影響的記錄進(jìn)行記錄，這些數(shù)據(jù)統(tǒng)稱為重做。重做日志的數(shù)據(jù)通過不斷增加的LSN值表示。

• 用于記錄數(shù)據(jù)修改后的記錄，順序記錄。
• 在崩潰恢復(fù)期間使用的基于磁盤的數(shù)據(jù)結(jié)構(gòu)，用于糾正不完整事務(wù)寫入的數(shù)據(jù)。

Redo日志的磁盤布局受配置選項(xiàng)innodb_log_file_size、innodb_log_group_home_dir和innodb_log_files_in_group的影響。重做日志操作的性能還受到日志緩沖區(qū)的影響innodb_log_buffer_size。

在崩潰恢復(fù)期間，InnoDB需查找一個(gè)寫入日志文件的檢查點(diǎn)。LSN出現(xiàn)在數(shù)據(jù)庫的磁盤映像之前對(duì)數(shù)據(jù)庫的所有修改，之后InnoDB從檢查點(diǎn)掃描日志文件，將日志修改應(yīng)用到數(shù)據(jù)庫。

Innodb_redo_log_archive_dirs重做日志進(jìn)行歸檔，主要考慮到備份操作進(jìn)行時(shí)，復(fù)制重做日志記錄的備份實(shí)用程序有時(shí)可能無法跟上重做日志生成的速度，從而導(dǎo)致重寫重做日志記錄而丟失這些記錄。除此之外也可以作為數(shù)據(jù)恢復(fù)的日志記錄。

6)常見檢查點(diǎn)壓力下的日志

出現(xiàn)這個(gè)page_cleaner的問題是臟頁產(chǎn)生的太快，導(dǎo)致頁面清理程序清理不過來。

目前解決方式，可以組合以下參數(shù)進(jìn)行調(diào)整：

• innodb_lru_scan_depth 值設(shè)置小。
• innodb_io_capacity，innodb_io_max_capacity 合理設(shè)置。
• innodb_max_dirty_page_pct 也設(shè)置的小一些。
• innodb_adaptive_hash_index 關(guān)閉。

三.總結(jié)

對(duì)于MySQL的checkpoint機(jī)制來說，是對(duì)IO和內(nèi)存做了平衡操作。

通過調(diào)節(jié)參數(shù)，對(duì)于不同的應(yīng)用系統(tǒng)，都是提升性能的一種方式，普遍情況下采取默認(rèn)方式。

另一個(gè)思路：重做日志可以無限增大，磁盤足夠大，同時(shí)緩沖池足夠大，能夠緩存所有數(shù)據(jù)，那么就不需要將緩沖池中的臟頁頻繁刷新。

關(guān)于作者

崔虎龍，云和恩墨MySQL技術(shù)顧問，長期服務(wù)于金融、游戲、物流等行業(yè)的數(shù)據(jù)中心，設(shè)計(jì)數(shù)據(jù)存儲(chǔ)架構(gòu)，并熟悉數(shù)據(jù)中心運(yùn)營管理的流程及規(guī)范，自動(dòng)化運(yùn)維等。擅長MySQL、Redis、MongoDB數(shù)據(jù)庫高可用設(shè)計(jì)和運(yùn)維故障處理、備份恢復(fù)、升級(jí)遷移、性能優(yōu)化。自學(xué)通過了MySQL OCP 5.6和MySQL OCP 5.7認(rèn)證。2年多開發(fā)經(jīng)驗(yàn)，10年數(shù)據(jù)庫運(yùn)維工作經(jīng)驗(yàn)，其中專職做MySQL工作8年;曾經(jīng)擔(dān)任過項(xiàng)目經(jīng)理、數(shù)據(jù)庫經(jīng)理、數(shù)據(jù)倉庫架構(gòu)師、MySQL技術(shù)專家、DBA等職務(wù);涉及行業(yè)：金融(銀行、理財(cái))、物流、游戲、醫(yī)療、重工業(yè)等。