一. 你的SQL語句為什么變慢了

1.回顧:?

? ? 掌柜的記憶==> 內(nèi)存

? ? 粉板==> redo log

? ? 賬本==> binlog

? ? 掌柜總要把記住的東西寫下來 ==> 內(nèi)存總要寫盤? ==> flush

2.名詞

? ? 臟頁: 內(nèi)存數(shù)據(jù)頁跟磁盤數(shù)據(jù)頁內(nèi)容不一致, 我們稱內(nèi)存頁為: 臟頁

? ? 干凈頁: 內(nèi)存數(shù)據(jù)寫入磁盤后 , 內(nèi)存和磁盤上的數(shù)據(jù)內(nèi)容一致后 , 稱為: 干凈頁

3.孔乙己賒賬例子:?

? ? 原來已欠10文 , 又要賒 9 文

1.孔乙己賒賬更新 和 flush 過程

4. 疑問: 什么時(shí)候會刷臟頁 ? ==> 粉板寫到賬本

? ? 場景1.粉板滿了 .? ? ==? ?redo log 寫滿了?

????????????==> 停止所有更新

? ? ? ? ? ? ==> checkpoint 推進(jìn) ,從 cp 到 cp'? ?== 把綠色部分的臟頁flush到磁盤

? ? ? ? ? ? ==> write pos 到cp' 之間的空間, 可以繼續(xù)寫入redo log .

2. redo log 狀態(tài)圖

場景2: 掌柜記不住了 == 內(nèi)存滿了.?

==> 需要淘汰頁?

==> 如果是臟頁, 就要先flush到磁盤.?

引申1 :? ? ?為什么不清內(nèi)存, 直接讀盤?? (我覺得清內(nèi)存很傻,因?yàn)榕獜?fù)雜了)

答: 1.從性能考慮, 如果內(nèi)存存在, 內(nèi)存肯定正確.?

? ? ? 2.如果內(nèi)存沒有, 磁盤肯定正確.?

場景3: 生意不忙, 或者打烊后 == mysql認(rèn)為系統(tǒng)空閑的時(shí)候

? ? 見縫插針, 一有機(jī)會就刷部分臟頁.

場景4: 年底清算 == mysql正常關(guān)閉?

5. 四種場景對性能的影響:?

? ? 場景1: redo log 寫滿 ==> 要避免, 因?yàn)樗懈露紩欢伦??

? ? 場景2: 內(nèi)存滿了 ==> 是常態(tài)

? ? ? ? 知識點(diǎn)1: innodb 使用緩沖池管理內(nèi)存, 緩沖池中內(nèi)存頁有三種狀態(tài):?

? ? ? ? ? ? 1.未使用

? ? ? ? ? ? 2.干凈頁

? ? ? ? ? ? 3.臟頁

? ? ? ? 知識點(diǎn)2: 當(dāng)要讀入的數(shù)據(jù)不在內(nèi)存中, 就必須向緩沖池申請一個(gè)數(shù)據(jù)頁.?

? ? ? ? ? ? ? ? ? ? ==> 只能把最久不使用的數(shù)據(jù)頁淘汰.?

? ? ? ? ? ? ? ? ? ? ==> 如果是臟頁, 要先寫盤.

? ? ? ? ? ? ? ? ? ? ==> 一個(gè)查詢要淘汰的臟頁太多,會導(dǎo)致響應(yīng)時(shí)間變長

? ? 總結(jié):? 要有臟頁比例控制機(jī)制.

6.innodb 刷臟頁的控制策略.?

1.要告訴innodb 宿主機(jī)的IO能力.?

? ? ? ?==> innodb_io_capacity 參數(shù)

? ? ? ?==> 建議設(shè)置成磁盤的IOPS?

疑問1 : 什么是IOPS ??

IOPS（Input/Output Operations Per Second）是一個(gè)用于計(jì)算機(jī)存儲設(shè)備（如硬盤（HDD）、固態(tài)硬盤（SSD）或存儲區(qū)域網(wǎng)絡(luò)（SAN））性能測試的量測方式, 可以視為是每秒的讀寫次數(shù)。

? ? ? ? ==> 可以通過fio工具測試 , 命令如下

? ? ? ? ==> 設(shè)置錯誤的現(xiàn)象:? mysql寫入很慢, tps很低, IO壓力不大.?

2.確定全力刷臟頁后, 要指定策略, 不能一直全力刷臟頁:?

? ? 1.影響因素:? 臟頁比例,? redolog 寫盤速度

? ? 2. innodb_max_dirty_pages_pct 臟頁比例上限? 默認(rèn)75%

? ? 3.F1 和 F2 如下圖:?

3. innodb 刷臟頁速度策略

? ? 總結(jié): 總之和臟頁比例 , redo log 寫盤速度有關(guān).?

? ? 原則1: 不要讓臟頁比例經(jīng)常接近75%?

? ? ==> innodb_buffer_pool_pages_dirty?

? ? ==> innodb_buffer_pool_pages_total? 臟頁比例是通過這兩個(gè)參數(shù)得到的.?

3.有趣的刷臟頁策略:?

? ?1.策略1 :? innodb_flush_neighbors

? ? ? ? what : flush時(shí), 如果目標(biāo)臟頁附近也是臟頁, 會一起刷.? 該機(jī)制會蔓延.

? ? ? ? why: 機(jī)械硬盤時(shí)代 減少很多隨機(jī)IO

? ? ? but: SSD 時(shí)代,? IOPS已經(jīng)不是瓶頸, innodb_flush_neighbors應(yīng)該設(shè)置成0 , 只刷自己,可以減少SQL語句的響應(yīng)時(shí)間.?

? ??????