深入解析MySQL檢查點(diǎn)

深入解析Mysql檢查點(diǎn)搜狐暢游DBA王洪權(quán)mydbalife@gmail.2011-07-06深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第1頁(yè)!summarryMysqllogfileMysql檢查點(diǎn)Mysqldoublewrite深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第2頁(yè)!基本操作深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第3頁(yè)!基本操作深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第4頁(yè)!基本操作深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第5頁(yè)!基本操作深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第6頁(yè)!.

為什么要采取異步刷新Why?深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第7頁(yè)!關(guān)于log工作原理.orczhou./index.php/2009/08/innodb-dirtyredo-log-2/深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第8頁(yè)!Logbuffer設(shè)置建議mysqladminvar|greplog_bufferinnodb_log_buffer_size|8388608通常設(shè)置范圍8~16M通常8M比較合適，除非有很多blob/text字段操作，或大事務(wù)操作。深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第9頁(yè)!innodb_log_file_size關(guān)于innodb_log_file_size大小設(shè)置如何找到最佳的均衡點(diǎn)呢？.mysqlperformanceblog./2006/07/03/choosing-proper-innodb_log_file_size/

.mysqlperformanceblog./2008/11/21/how-to-calculate-a-good-innodb-log-file-size/深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第10頁(yè)!innodb_log_file_size設(shè)置深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第11頁(yè)!修改innodb_log_file_size1干凈的關(guān)閉數(shù)據(jù)庫(kù)，刪除日志文件，修改f中innodb_log_file_size，重啟mysql2干凈的關(guān)閉數(shù)據(jù)庫(kù)，rename日志文件，修改f中innodb_log_file_size，重啟mysql深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第12頁(yè)!Innodb日志機(jī)制深入分析深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第13頁(yè)!管理機(jī)制Checkpoint和日志緊密相關(guān)，將日志和Checkpoint一起說(shuō)明，詳細(xì)的實(shí)現(xiàn)機(jī)制如下：Innodb日志機(jī)制深入分析深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第14頁(yè)!對(duì)應(yīng)這4個(gè)階段，系統(tǒng)記錄了4個(gè)日志相關(guān)的信息，用于其它各種處理使用：Logsequencenumber（LSN1）：當(dāng)前系統(tǒng)LSN最大值，新的事務(wù)日志LSN將在此基礎(chǔ)上生成（LSN1+新日志的大?。?；Logflushedupto（LSN2）：當(dāng)前已經(jīng)寫(xiě)入日志文件的LSN；Oldestmodifieddatalog（LSN3）：當(dāng)前最舊的臟頁(yè)數(shù)據(jù)對(duì)應(yīng)的LSN，寫(xiě)Checkpoint的時(shí)候直接將此LSN寫(xiě)入到日志文件；Lastcheckpointat（LSN4）：當(dāng)前已經(jīng)寫(xiě)入Checkpoint的LSN；

對(duì)于系統(tǒng)來(lái)說(shuō)，以上4個(gè)LSN是遞減的，即：LSN1>=LSN2>=LSN3>=LSN4.Innodb日志機(jī)制深入分析深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第15頁(yè)!Innodb日志機(jī)制深入分析Innodb的數(shù)據(jù)并不是實(shí)時(shí)寫(xiě)盤(pán)的，為了避免宕機(jī)時(shí)數(shù)據(jù)丟失，保證數(shù)據(jù)的ACID屬性，Innodb至少要保證數(shù)據(jù)對(duì)應(yīng)的日志不能丟失。對(duì)于不同的情況，Innodb采取不同的對(duì)策：宕機(jī)導(dǎo)致日志丟失

Innodb有日志刷盤(pán)機(jī)制，可以通過(guò)innodb_flush_log_at_trx_mit參數(shù)進(jìn)行控制；日志覆蓋導(dǎo)致日志丟失Innodb日志文件大小是固定的，寫(xiě)入的時(shí)候通過(guò)取余來(lái)計(jì)算偏移量，這樣存在兩個(gè)LSN寫(xiě)入到同一位置的可能，后面寫(xiě)的把前面寫(xiě)得就覆蓋了，以“寫(xiě)入機(jī)制”章節(jié)的樣例為例，LSN＝100000000和LSN＝1600000000兩個(gè)日志的偏移量是相同的了。這種情況下，為了保證數(shù)據(jù)一致性，必須要求LSN=1000000000對(duì)應(yīng)的臟頁(yè)數(shù)據(jù)都已經(jīng)刷到磁盤(pán)中，也就是要求Lastcheckpoint對(duì)應(yīng)的LSN一定要大于1000000000，否則覆蓋后日志也沒(méi)有了，數(shù)據(jù)也沒(méi)有刷盤(pán)，一旦宕機(jī)，數(shù)據(jù)就丟失了。深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第16頁(yè)!通常這里的sharp指的是刷新BP中所有的臟頁(yè)到datafile中。臟頁(yè)刷新完畢的時(shí)候產(chǎn)生一個(gè)sharpcheckpoint例如關(guān)閉數(shù)據(jù)庫(kù)的時(shí)候發(fā)生sharp檢查點(diǎn)大概步驟是1停止所有的更新操作2刷新所有的臟頁(yè)到磁盤(pán)3寫(xiě)入當(dāng)前的檢查點(diǎn)信息到日志文件4把檢查點(diǎn)信息寫(xiě)入到每個(gè)datafile中。Sharpcheckpoint介紹深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第17頁(yè)!每1S若bufferpool中的臟頁(yè)比率超過(guò)了srv_max_buf_pool_modified_pct=75，則進(jìn)行Checkpoint，刷臟頁(yè)，flushPCT_IO(100)的dirtypages=200若采用adaptiveflushing，則計(jì)算flushrate，進(jìn)行必要的flushflushPCT_IO(100)的dirtypages=200每10S若bufferpool中的臟頁(yè)比率超過(guò)了70%，flushPCT_IO(100)的dirtypagesdirtypages=200若bufferpool中的臟頁(yè)比率未超過(guò)70%，flushPCT_IO(10)的dirtypages=20Checkpoint觸發(fā)條件深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第18頁(yè)!和檢查點(diǎn)相關(guān)的數(shù)據(jù)結(jié)構(gòu)1.LRulist2.Flushlist深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第19頁(yè)!深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第20頁(yè)!Gettotalbufferpoolstatistics.*/buf_get_total_list_len(for(i=0;i<srv_buf_pool_instances;i++){buf_pool_t*buf_pool;buf_pool=buf_pool_from_array(i);*LRU_len+=UT_LIST_GET_LEN(buf_pool->LRU);*free_len+=UT_LIST_GET_LEN(buf_pool->free);*flush_list_len+=UT_LIST_GET_LEN(buf_pool->flush_list);}}innodb_max_dirty_pages_pct在數(shù)據(jù)庫(kù)源代碼中的體現(xiàn)buf0buf.c深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第21頁(yè)!反映在數(shù)據(jù)庫(kù)上的臟頁(yè)比率臟頁(yè)的比率計(jì)算深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第22頁(yè)!相關(guān)性能參數(shù)在數(shù)據(jù)庫(kù)上的反映深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第23頁(yè)!Checkpoint觸發(fā)深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第24頁(yè)!函數(shù)流程：buf0buf.cbuf_flush_get_desired_flush_rate1.從buf_pool_t結(jié)構(gòu)中，獲得總dirtypage的數(shù)量2.計(jì)算最近一段時(shí)間之內(nèi)，redo日志產(chǎn)生的平均速度其中，BUF_FLUSH_STAT_N_INTERVAL=20S，20S內(nèi)的平均redo產(chǎn)生速度/**Numberofintervalsforwhichwekeepthehistoryofthesestats.Eachintervalis1second,definedbytherateatwhichsrv_error_monitor_thread()callsbuf_flush_stat_update().*/#defineBUF_FLUSH_STAT_N_INTERVAL20flush的統(tǒng)計(jì)信息，每隔20S會(huì)被buf_flush_stat_update函數(shù)重置Checkpoint觸發(fā)檢查點(diǎn)深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第25頁(yè)!檢查點(diǎn)更新流程流程一每10秒更新檢查點(diǎn)流程二在高IO系統(tǒng)中每1秒更新檢查點(diǎn)深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第26頁(yè)!Innodb日志機(jī)制深入分析深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第27頁(yè)!Innodb日志機(jī)制深入分析概念計(jì)算含義Ckpasync日志空間大小*31/32強(qiáng)制寫(xiě)Checkpoint，此時(shí)事務(wù)還可以繼續(xù)執(zhí)行，所以為async，對(duì)事務(wù)的執(zhí)行速度沒(méi)有影響（間接影響也不大，因?yàn)閷?xiě)Checkpoint的操作比較簡(jiǎn)單）Ckpsync日志空間大小*64/64強(qiáng)制寫(xiě)Checkpoint，此時(shí)事務(wù)停止執(zhí)行，所以為sync，但由于寫(xiě)Checkpoint的操作比較簡(jiǎn)單，即使阻塞，時(shí)間也很短深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第28頁(yè)!影響檢查點(diǎn)整檢查點(diǎn)的參數(shù)深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第29頁(yè)!深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第30頁(yè)!InnodbDoubleWrite內(nèi)存中存在一塊區(qū)域，大小為2M系統(tǒng)表空間存在一塊區(qū)域，大小為2M深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第31頁(yè)!深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第32頁(yè)!深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第33頁(yè)!Doublewrite

buffer寫(xiě)入量可以看到比例遠(yuǎn)小于64:1說(shuō)明系統(tǒng)寫(xiě)入壓力并不大深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第34頁(yè)!Q&A深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第35頁(yè)!基本操作深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第36頁(yè)!基本操作深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第37頁(yè)!基本操作深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第38頁(yè)!基本操作深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第39頁(yè)!為什么要采取異步刷新1Thisisanoptimization2Itcallitwritebining深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第40頁(yè)!innodb_flush_log_at_trx_mit深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第41頁(yè)!Logbuffer設(shè)置showglobalstatuslike'%Innodb_log_waits%';+++|Variable_name|Value|+++|Innodb_log_waits|0|+++Innodb_log_waits

Thenumberoftimesthatthelogbufferwastoosmallandawaitwasrequiredforittobeflushedbeforecontinuing.深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第42頁(yè)!innodb_log_file_size深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第43頁(yè)!大概7M每分鐘，那么一個(gè)小時(shí)是多少呢，7*60=420M,所以單個(gè)logfile大小256M大小足夠。2008ByBaronSchwartz.mysqlperformanceblog./2008/11/21/how-to-calculate-a-good-innodb-log-file-size/innodb_log_file_size設(shè)置深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第44頁(yè)!Log寫(xiě)入

LSN實(shí)際上對(duì)應(yīng)日志文件的偏移量，新的LSN＝舊的LSN+寫(xiě)入的日志大小。舉例如下：LSN＝1G，日志文件大小總共為600M，本次寫(xiě)入512字節(jié)，則實(shí)際寫(xiě)入操作為：l求出偏移量：由于LSN數(shù)值遠(yuǎn)大于日志文件大小，因此通過(guò)取余方式，得到偏移量為400M；寫(xiě)入日志：找到偏移400M的位置，寫(xiě)入512字節(jié)日志內(nèi)容，下一個(gè)事務(wù)的LSN就是1000000512；Innodb日志機(jī)制深入分析深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第45頁(yè)!Checkpoint寫(xiě)入Innodb實(shí)現(xiàn)了FuzzyCheckpoint的機(jī)制，每次取到最老的臟頁(yè)，然后確保此臟頁(yè)對(duì)應(yīng)的LSN之前的LSN都已經(jīng)寫(xiě)入日志文件，再將此臟頁(yè)的LSN作為Checkpoint點(diǎn)記錄到日志文件，意思就是“此LSN之前的LSN對(duì)應(yīng)的日志和數(shù)據(jù)都已經(jīng)寫(xiě)入磁盤(pán)文件”。恢復(fù)數(shù)據(jù)文件的時(shí)候，Innodb掃描日志文件，當(dāng)發(fā)現(xiàn)LSN小于Checkpoint對(duì)應(yīng)的LSN，就認(rèn)為恢復(fù)已經(jīng)完成。Checkpoint寫(xiě)入的位置在日志文件開(kāi)頭固定的偏移量處，即每次寫(xiě)Checkpoint都覆蓋之前的Checkpoint信息。Innodb日志機(jī)制深入分析深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第46頁(yè)!如上圖所示，Innodb的一條事務(wù)日志共經(jīng)歷4個(gè)階段：創(chuàng)建階段：事務(wù)創(chuàng)建一條日志；日志刷盤(pán)：日志寫(xiě)入到磁盤(pán)上的日志文件；數(shù)據(jù)刷盤(pán)：日志對(duì)應(yīng)的臟頁(yè)數(shù)據(jù)寫(xiě)入到磁盤(pán)上的數(shù)據(jù)文件；寫(xiě)CKP：日志被當(dāng)作Checkpoint寫(xiě)入日志文件；Innodb日志機(jī)制深入分析深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第47頁(yè)!Innodb日志機(jī)制深入分析深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第48頁(yè)!1Sharp檢查點(diǎn)。2FuzzycheckpointMysql檢查點(diǎn)的類型深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第49頁(yè)!1受參數(shù)innodb_max_dirty_pages_pct影響2percona中引入innodb_adaptive_flushing_method3根據(jù)日志大小進(jìn)行適時(shí)的刷新Fuzzycheckpoint深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第50頁(yè)!深入理解內(nèi)存中的數(shù)據(jù)結(jié)構(gòu)內(nèi)存結(jié)構(gòu)中bufferpool中的數(shù)據(jù)結(jié)構(gòu)FreelistLRUlistFlushlist深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第51頁(yè)!關(guān)于LRUSpecifiestheapproximatepercentageoftheInnoDBbufferpoolusedfortheoldblocksublist.Therangeofvaluesis5to95.Thedefaultvalueis37(thatis,3/8ofthepool).深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第52頁(yè)!關(guān)于FLUShlist實(shí)際上就是所有的臟頁(yè)的一個(gè)先后的修改順序排列深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第53頁(yè)!buf_get_modified_ratio_pct(void){ulintratio;ulintlru_len=0;ulintfree_len=0;ulintflush_list_len=0;buf_get_total_list_len(&lru_len,&free_len,&flush_list_len);ratio=(100*flush_list_len)/(1+lru_len+free_len);/*1+istheretoavoiddivisionbyzero*/return(ratio);}innodb_max_dirty_pages_pct在數(shù)據(jù)庫(kù)源代碼中的體現(xiàn)buf0buf.c深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第54頁(yè)!可以看出數(shù)據(jù)庫(kù)典型反映在數(shù)據(jù)庫(kù)上的臟頁(yè)比率深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第55頁(yè)!Checkpoint觸發(fā)深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第56頁(yè)!計(jì)算過(guò)去一段時(shí)間內(nèi)，flush的平均速度；與當(dāng)前需要的flush速度其中，BUF_FLUSH_STAT_N_INTERVAL=20S不變，計(jì)算的仍舊是過(guò)去20S內(nèi)的平均flush速度若當(dāng)前所需速度>20S內(nèi)的平均速度，則adaptiveflushing會(huì)嘗試進(jìn)行一次flush操作。flush的dirtypages數(shù)量仍舊是PCT_IO(100)，200個(gè)dirtypages。Checkpoint觸發(fā)檢查點(diǎn)深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第57頁(yè)!刷新臟頁(yè)到磁盤(pán)的算法首先計(jì)算可選的neighborsflush臟頁(yè)之前，必須保證臟頁(yè)對(duì)應(yīng)的日志已經(jīng)寫(xiě)回日志文件將數(shù)據(jù)拷貝到doublewritememory將doublewritememory寫(xiě)出到diskdoublewritebuffer的寫(xiě)，為同步寫(xiě)，調(diào)用在doublewritebuffer被成功flush到disk之后，對(duì)應(yīng)的dirtypages不會(huì)再丟失數(shù)據(jù)。此時(shí)再將doublewritebuffer對(duì)應(yīng)的dirtypages寫(xiě)出到disk標(biāo)識(shí)當(dāng)前flush操作結(jié)束(buf_flush_end)收集當(dāng)前flush操作的統(tǒng)計(jì)信息更新檢查點(diǎn)信息深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽的是第58頁(yè)!Innodb日志機(jī)制深入分析為了解決第二種情況導(dǎo)致數(shù)據(jù)丟失的問(wèn)題，Innodb實(shí)現(xiàn)了一套日志保護(hù)機(jī)制，詳細(xì)實(shí)現(xiàn)如下：

為了解決第二種情況導(dǎo)致數(shù)據(jù)丟失的問(wèn)題，Innodb實(shí)現(xiàn)了一套日志保護(hù)機(jī)制，詳細(xì)實(shí)現(xiàn)如下：“sync”pointisatabout7/8ofinnodb_log_file_size,andthe“async”pointisatabout6/8=3/4ofinnodb_log_file_size.深入解析MySQL檢查點(diǎn)共68頁(yè)，您現(xiàn)在瀏覽