一條 SQL 語句判导，正臣蹈福快，有時特別慢眼刃，難復(fù)現(xiàn)绕辖，隨機持續(xù)時間短。
一擂红、SQL 語句為什么變“慢”
第 2 篇文章介紹 WAL 機制仪际。InnoDB 更新時，只做寫日志(redo log重做日志)這個磁盤操作昵骤。酒店記賬粉板树碱，更新內(nèi)存寫完redo log 返回給客戶端，更新成功变秦。
掌柜記憶是內(nèi)存成榜。更新賬本，內(nèi)存里寫入磁盤 flush蹦玫。flush前赎婚，賒賬總額和賬本記錄不一致刘绣。今天賒賬只在粉板。
不一致時挣输，內(nèi)存頁為“臟頁”纬凤。一致，為“干凈頁”撩嚼。
原來欠 10 文停士，又賒 9 文。
MySQL “抖”瞬間完丽，可能就是刷臟頁（flush）：將內(nèi)存頁寫入磁盤恋技。
二、什么情況會引發(fā)數(shù)據(jù)庫 flush舰涌？
1、粉板滿了你稚，再有賒賬瓷耙，放下手里活兒，粉板記錄擦掉一些刁赖，繼續(xù)記搁痛。擦掉前記賬本。redo log 寫滿宇弛，停止更新鸡典，checkpoint 往前推，redo log留空繼續(xù)寫枪芒。
checkpoint 不是隨便往前修改位置就可以彻况。從 CP 推到 CP’，兩點之間日志舅踪，所有臟頁 flush 磁盤纽甘。write pos 到 CP’可再寫。
2.生意太好記不住了抽碌，找賬本把孔乙己這筆賬先加進去悍赢。1）寫binlog
內(nèi)存不足，需新內(nèi)存頁货徙，內(nèi)存不夠用左权，淘汰數(shù)據(jù)頁。如果淘汰“臟頁”痴颊，先將臟頁寫磁盤赏迟。2）寫磁盤，空出內(nèi)存
內(nèi)存直接淘汰掉蠢棱，下次從磁盤讀入數(shù)據(jù)頁瀑梗，拿 redo log 出來應(yīng)用不就行烹笔？性能考慮。如果刷臟頁一定寫盤抛丽，保證數(shù)據(jù)頁兩種狀態(tài)：
（1）內(nèi)存里存在谤职，直接返回；
（2）內(nèi)存沒有數(shù)據(jù)亿鲜，文件上肯定是正確允蜈，讀入內(nèi)存后返回（效率高）。
3.生意不忙蒿柳，更新賬本饶套。MySQL“空閑”刷“臟頁”。
4.年底結(jié)賬垒探。MySQL 正常關(guān)閉臟頁flush 到磁盤上妓蛮，下次啟動直接從磁盤上讀，快圾叼。
四種場景對性能影響
三蛤克、四不會太關(guān)注“性能”。
(1)redo log 滿夷蚊， InnoDB 盡量避免构挤。系統(tǒng)不能更新，監(jiān)控上看惕鼓，更新數(shù)跌為 0筋现。
(2)內(nèi)存不夠用，常態(tài)箱歧。InnoDB 緩沖池（buffer pool）中的內(nèi)存頁有三種狀態(tài)：
????沒用矾飞； 用了干凈頁； 用了臟頁呀邢。
策略：盡量使用內(nèi)存凰慈，長時間運行庫，未被使用頁很少驼鹅。
要讀入數(shù)據(jù)頁沒在內(nèi)存微谓，到緩沖池申請。從內(nèi)存中淘汰數(shù)據(jù)頁（最久不用）：干凈頁输钩，釋放復(fù)用豺型；臟頁，刷到磁盤买乃，變干凈復(fù)用姻氨。
刷臟頁也會影響性能，控制臟頁比例剪验，避免：
1. 淘汰臟頁太多肴焊，響應(yīng)時間明顯變長前联；
2.? 日志寫滿，更新堵住娶眷，寫性能 0似嗤。
三、InnoDB 刷臟頁的控制策略
告訴 InnoDB 所在主機的 IO 能力知道全力刷臟頁速度
innodb_io_capacity 設(shè)置成磁盤的IOPS（用于計算機存儲設(shè)備届宠，如硬盤（HDD）烁落、固態(tài)硬盤（SSD）或存儲區(qū)域網(wǎng)絡(luò)（SAN））性能測試的量測方式。
通過 fio工具測試磁盤隨機讀寫命令：
?fio -filename=$filename -direct=1 -iodepth 1? -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10? -group_reporting -name=mytest
沒正確地設(shè)置innodb_io_capacity 參數(shù)豌注，導(dǎo)致性能問題伤塌。MySQL 寫慢，TPS 很低轧铁，數(shù)據(jù)庫主機的 IO 壓力不大：
主機磁盤SSD每聪，innodb_io_capacity= 300。InnoDB 系統(tǒng)能力差齿风，刷臟頁比臟生成速度還慢药薯，臟頁累積，影響性能聂宾。
不能一直全力刷果善，還需服務(wù)用戶請求诊笤。
3.1控制刷臟頁速度系谐，參考因素
刷太慢，1讨跟、臟頁太多纪他，2、redo log 寫滿
InnoDB 先單獨算出兩個數(shù)字：
1晾匠、根據(jù)當前臟頁比例 M茶袒，算出0 ~100數(shù)字，計算數(shù)字偽代碼：
多關(guān)注臟頁比例凉馆，不要讓它經(jīng)常接近?75%
F1(M)
{
? if M>=innodb_max_dirty_pages_pct （臟頁比例上限薪寓，默認75%）then
????? return 100;
? return 100*M/innodb_max_dirty_pages_pct;
}
2、InnoDB 每次寫日志序號澜共，跟 checkpoint 序號差?= N向叉。根據(jù)N 算出 0 ~100 數(shù)字，公式F2(N)嗦董。算法復(fù)雜母谎，N 越大，算出值越大京革。
?F1(M) 和 F2(N) 取較大值為 R奇唤，innodb_io_capacity 乘以 R% 控制刷臟頁的速度幸斥。
?F1、F2 通過臟頁比例和 redo log 寫入速度算出來的兩個值咬扇。
臟頁比例：nnodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的甲葬，命令：
mysql> select? VARIABLE_VALUE into @a from global_status where VARIABLE_NAME =? 'Innodb_buffer_pool_pages_dirty';
select? VARIABLE_VALUE into @b from global_status where VARIABLE_NAME =? 'Innodb_buffer_pool_pages_total';
select @a/@b;
3.2有趣的策略：
查詢請求需先 flush 掉臟頁，比平時慢冗栗。MySQL一個機制演顾，讓查詢更慢：刷臟頁時，旁邊是臟頁隅居，“鄰居”一起刷掉钠至；相鄰還是臟頁一起刷。
innodb_flush_neighbors控制這個行為胎源，= 1“連坐”機制棉钧，= 0 不找鄰居（默認）
找“鄰居”機械硬盤（隨機 IOPS 幾百）時代有意義，減少很機 IO涕蚤。相同邏輯減少隨機 IO 性能提升宪卿。
SSD 這類 IOPS 高的設(shè)備，innodb_flush_neighbors = 0万栅。 IOPS 不是瓶頸佑钾，“只刷自己”更快。
小結(jié)
WAL 的概念（延續(xù)第 2 篇中介紹）
解釋這個機制后續(xù)需要刷臟頁操作和執(zhí)行時機烦粒。
WAL 技術(shù)休溶，數(shù)據(jù)庫將隨機寫轉(zhuǎn)換成順序?qū)?/b>，大大提升數(shù)據(jù)庫性能扰她。
內(nèi)存臟頁問題：臟頁會被后臺線程自動 flush兽掰，也由于數(shù)據(jù)頁淘汰而觸發(fā) flush，刷臟頁過程占用資源徒役，可能讓更新和查詢語句響應(yīng)時間長孽尽。介紹了控制刷臟頁方法和監(jiān)控方式。
思考題
內(nèi)存128GB忧勿、innodb_io_capacity = 20000 大規(guī)格實例杉女，正常建議將 redo log 設(shè)置成 4 個 1GB 的文件。
不慎將 redo log 設(shè)置成 1 個 100M 的文件鸳吸，發(fā)生什么情況熏挎？為什么？
答：每次事務(wù)提交都寫 redo log层释，設(shè)置小很快被寫滿婆瓜，write pos 一直追 CP。停止所有更新，推進 checkpoint廉白。
現(xiàn)象:磁盤壓力很小个初，數(shù)據(jù)庫出現(xiàn)間歇性性能下跌。
評論1
刷臟頁對應(yīng)redo log位置隨機猴蹂，redo log不同位置刷掉院溺，redo log處理不是很麻煩？（合并間隙磅轻，移動位置珍逸？）
redo log優(yōu)勢：將磁盤隨機轉(zhuǎn)換成順序?qū)懀@樣不是redo log里隨機讀寫么聋溜？?
答：刷臟頁過程不用動redo log文件谆膳。redo log“重放”時，數(shù)據(jù)頁刷過撮躁，會識別跳過漱病。
評論2
redo log保證ACID的D。
1.臟頁保存到磁盤,checkpoint往前推
2.redo log記錄undo變化,undo log buffer持久化undo log
3.innodb_flush_log_at_trx_commit=0,內(nèi)存redo log持久化到磁盤
4.redo log記錄change buffer改變,要把change buffer purge到idb
連change buffer的優(yōu)化也沒意義了
以及merge change buffer.merge也是臟頁,持久化到磁盤
上述都是占用系統(tǒng)I/O,影響DML,操作頻繁,'抖'向過冬把曼。
select查詢時間相對會更快杨帽。臟頁變少，不用淘汰,直接用干凈頁嗤军。宕機恢復(fù),速度快注盈，checkpoint很接近LSN,恢復(fù)數(shù)據(jù)頁相對較少
評論3
抖動現(xiàn)象，問題：
1）Innodb_buffer_pool_pages_total百萬級別叙赚，不像人為設(shè)置老客，怎么來？
2）Innodb_buffer_pool_pages_dirty4萬多開始flush了纠俭，臟頁比例75沿量，遠達不到的浪慌，ssd磁盤冤荆，innodb_io_capacity=200，可以高权纤。flush觸發(fā)條件钓简，內(nèi)存不夠，redo log 滿汹想，這個場景是哪種情況呢
回復(fù): 1）是innodb 數(shù)據(jù)頁總外邓，過百萬正常，16K一個古掏，Bufree pool size 16G 就是100萬
2）io_capacity設(shè)太小
評論4
buffer pool里維護臟頁列表损话，假設(shè)redo log 的 checkpoint 記錄 LSN （日志序列號）= 10，內(nèi)存中一干凈頁有修改LSN為12，大于 checkpoint 的LSN丧枪，寫redo log同時該頁也會被標記為臟頁記錄到臟頁列表中光涂，現(xiàn)內(nèi)存不足，該頁被淘汰刷到磁盤拧烦，LSN=12從臟頁列表中移除忘闻，redo log推進checkpoint，到LSN=12log時恋博，為干凈頁跳過齐佳。
評論5
redo log寫滿：redo log對應(yīng)臟頁flush，釋放空間债沮。問題：
1炼吴、臟頁flush到磁盤上，接將臟頁數(shù)據(jù)覆蓋到對應(yīng)磁盤上數(shù)據(jù)疫衩？是的
2缺厉、redo log斷電重啟，內(nèi)存丟失隧土，通過redo log數(shù)據(jù)恢復(fù)提针，redo log怎么釋放空間？
重啟了就從checkpoint 位置往后掃曹傀。 之前刷過盤, 不會重復(fù)應(yīng)用redo log添瓷。
評論6
如何判斷數(shù)據(jù)頁是否在內(nèi)存當中？
每頁又編號险掀。拿編號去內(nèi)存看腋妙，沒有，就去磁盤
