我的學習記錄，可能有誤請諒解，提供了一些原始碼介面供有興趣的朋友除錯。
版本：5.7.17

一、LSN

innodb的lsn和oracle的scn一樣，是一個重要的概念。是整個資料庫資料同步的一種統一辨別標準，在很多地方都使用到了LSN比如

• 在flush list中正是是使用page的oldest lsn作為連結串列的條件
  參考buf_page_t類中的 lsn_t oldest_modification;變數
• 在checkpoint中記錄的也是lsn
  參考宏 LOG_CHECKPOINT_LSN
• 在物理檔案中每個page最後的重新整理lsn
  參考宏FIL_PAGE_LSN
• 在寫日誌落盤的時候也是以lsn為標準的
  參考函式log_write_up_to

實際上lsn就是表示的日誌量的位元組數，是一個累加的值，在5.7中表現為:

/* Type used for all log sequence number storage and arithmetics */ typedef ib_uint64_t lsn_t; 

及一個8位元組非負的整數。最大值及2的64次方。有了這種物理上概念，lsn很容易換算為當前日誌的偏移量。

二、innodb中檢查點的理解

這裡我只討論正常執行的情況下檢查點。innodb中類似oracle的增量檢查點。正常執行checkpoint是由master執行緒觸發。我們知道髒資料透過page clean執行緒和lru manager執行緒是在不斷寫盤的，那麼在進行異常重啟的的時候我們必須要知道一個恢復的起點，但是這個起點是不能記錄在記憶體中必要固化到磁碟，恢復的時候讀取這個點以後的redo進行恢復，而checkpoint就是完成這個事情下面是checkpoint的執行流程。

正常情況下master執行緒會每秒進行檢查點其作用有(參考log_checkpoint函式)：

• 檢查是否有自上次檢查點以來的髒資料寫盤了。
• 如果有則在redo裡面會為每個修改過的檔案寫入MLOG_FILE_NAME,完成後寫入一個總MLOG_CHECKPOINT(參考fil_names_clear函式)。
  1、MLOG_FILE_NAME主要記錄至上次檢查點以來更改過的資料檔案。
  2、MLOG_CHECKPOINT主要記錄檢查點的lsn。
  這個步驟會遍歷fil_system->named_spaces用於查詢是否有自上次檢查點以來修改過的檔案連結串列。
• 如果有則在redo log header中寫入相應的檢查點資訊包含(非同步寫)。

實際上我們可以理解檢查點就是由master執行緒每秒醒來檢視一下髒資料寫到哪裡了，然後將其記錄到合適的位置，以備carsh recovery使用。(參考srv_master_thread函式)

三、show engine innodb中的檢查點資訊

下面是一個沒有任何更新操作的庫的資訊如下：

Log sequence number 697794162
Log flushed up to   697794162
Pages flushed up to 697794162
Last checkpoint at  697794153 

• Log sequence number：已經寫到log buffer中的lsn。
  參考mtr_t::Command::finish_write函式。
• Log flushed up to：已經寫到日誌檔案的redo的lsn。
  參考log_write_flush_to_disk_low函式。
• Pages flushed up to ：此lsn之前的髒資料都已經寫到了資料檔案。
  參考log_buf_pool_get_oldest_modification函式。
• Last checkpoint at ：最後一次檢查點記錄到了什麼位置。
  參考next_checkpoint_lsn函式。

下面是這段輸出的原始碼：

 fprintf(file, "Log sequence number " LSN_PF "\n" "Log flushed up to   " LSN_PF "\n" "Pages flushed up to " LSN_PF "\n" "Last checkpoint at  " LSN_PF "\n",
        log_sys->lsn,
        log_sys->flushed_to_disk_lsn,
        log_buf_pool_get_oldest_modification(),
        log_sys->last_checkpoint_lsn); 

一般來講Log sequence number >Log flushed up to> Pages flushed up to>Last checkpoint at ，但是這裡注意一下。Pages flushed up to 697794162和Last checkpoint at 697794153，顯然這裡是一個沒有任何操作的庫所以Pages flushed up to應該和Last checkpoint at 相等，但是這裡存在差值，差值為：

• 697794162-697794153 = 9

這剛好是MLOG_CHECKPOINT的長度原始碼片段如下：

oldest_lsn <= log_sys->last_checkpoint_lsn + SIZE_OF_MLOG_CHECKPOINT /** Size of a MLOG_CHECKPOINT record in bytes.
The record consists of a MLOG_CHECKPOINT byte followed by
mach_write_to_8(checkpoint_lsn). */ #define SIZE_OF_MLOG_CHECKPOINT 9 

四、我所debug的幾種redo寫盤的時機

• master 執行緒每秒呼叫 棧幀(可能是idle可能是active 和檢測是否需要插入快取合併有關)

#0  log_group_write_buf (group=0x33f29f8, buf=0x7fffa5b38000 "\200\024", len=512, pad_len=0, start_lsn=697764864, new_data_offset=166) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1145 #1  0x0000000001a50f95 in log_write_up_to (lsn=697765068, flush_to_disk=true) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1493 #2  0x0000000001a51163 in log_buffer_sync_in_background (flush=true) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1553 #3  0x0000000001b84bd1 in srv_sync_log_buffer_in_background () at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/srv/srv0srv.cc:2312 #4  0x0000000001b85666 in srv_master_do_idle_tasks () at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/srv/srv0srv.cc:2586 #5  0x0000000001b85b6b in srv_master_thread (arg=0x0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/srv/srv0srv.cc:2744 

• master 執行緒每秒checkpoint呼叫 (可能是idle可能是active 和檢測是否需要插入快取合併有關)

#0  log_group_write_buf (group=0x33f29f8, buf=0x7fffa5a38000 "\200\024\002", len=1024, pad_len=0, start_lsn=697789952, new_data_offset=139) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1145 #1  0x0000000001a50f95 in log_write_up_to (lsn=697790725, flush_to_disk=true) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1493 #2  0x0000000001a52247 in log_checkpoint (sync=true, write_always=false) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1934 #3  0x0000000001b856f2 in srv_master_do_idle_tasks () at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/srv/srv0srv.cc:2596 #4  0x0000000001b85b6b in srv_master_thread (arg=0x0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/srv/srv0srv.cc:2744 

• page clean 執行緒呼叫 棧幀

#0  log_group_write_buf (group=0x33f29f8, buf=0x7fffa5a38000 "\200\024\002", len=13312, pad_len=1024, start_lsn=697778176, new_data_offset=468) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1145 #1  0x0000000001a50f95 in log_write_up_to (lsn=697790015, flush_to_disk=true) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1493 #2  0x0000000001c704c7 in buf_flush_write_block_low (bpage=0x7fffc0cae940, flush_type=BUF_FLUSH_LIST, sync=false) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/buf/buf0flu.cc:1035 #3  0x0000000001c70cea in buf_flush_page (buf_pool=0x33247d8, bpage=0x7fffc0cae940, flush_type=BUF_FLUSH_LIST, sync=false) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/buf/buf0flu.cc:1237 #4  0x0000000001c717f4 in buf_flush_try_neighbors (page_id=..., flush_type=BUF_FLUSH_LIST, n_flushed=0, n_to_flush=25) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/buf/buf0flu.cc:1466 

• 當前執行緒commit 呼叫棧幀如下：

#0  log_group_write_buf (group=0x33f29f8, buf=0x7fffa5a38000 "\200\024\002", len=2560, pad_len=0, start_lsn=697762816, new_data_offset=230) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1145 #1  0x0000000001a50f95 in log_write_up_to (lsn=697765030, flush_to_disk=true) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1493 #2  0x0000000001a51087 in log_buffer_flush_to_disk (sync=true) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1524 #3  0x00000000019a9157 in innobase_flush_logs (hton=0x2e9fdd0, binlog_group_flush=true) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:4407 #4  0x0000000000f65893 in flush_handlerton (thd=0x0, plugin=0x7ffff03588e8, arg=0x7ffff0358944) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:2606 #5  0x00000000015d7716 in plugin_foreach_with_mask (thd=0x0, func=0xf65835 <flush_handlerton(THD*, plugin_ref, void*)>, type=1, state_mask=4294967287,  arg=0x7ffff0358944) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_plugin.cc:2318 #6  0x0000000000f658ef in ha_flush_logs (db_type=0x0, binlog_group_flush=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:2617 #7  0x000000000185733d in MYSQL_BIN_LOG::process_flush_stage_queue (this=0x2e02c80, total_bytes_var=0x7ffff0358a88, rotate_var=0x7ffff0358a87,  out_queue_var=0x7ffff0358a78) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8541 #8  0x000000000185899f in MYSQL_BIN_LOG::ordered_commit (this=0x2e02c80, thd=0x7fff2c000b70, all=false, skip_commit=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:9189 #9  0x000000000185700c in MYSQL_BIN_LOG::commit (this=0x2e02c80, thd=0x7fff2c000b70, all=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8440 #10 0x0000000000f63df8 in ha_commit_trans (thd=0x7fff2c000b70, all=false, ignore_global_read_lock=false) 

• innodb shutdown(未debug)
• redo buffer不足(未debug)

作者微信：

微信.jpg