Shulou(Shulou.com)05/31 Report--

Editor to share with you about the Innodb checkpoint and the timing of redo writing, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

1. LSN

Innodb's lsn, like oracle's scn, is an important concept. It is a unified discrimination standard for data synchronization of the whole database, and LSN is used in many places, such as

In flush list, it is the oldest lsn of page that is used as a condition of the linked list to refer to the lsn_t oldest_modification; variable in the buf_page_t class

What is recorded in checkpoint is also the lsn reference macro LOG_CHECKPOINT_LSN

The last refresh lsn reference macro FIL_PAGE_LSN for each page in the physical file

When writing the log, it is also the reference function log_write_up_to based on lsn.

In fact, lsn is the number of bytes of log volume, which is a cumulative value, which is shown in 5.7as follows:

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

And an 8-byte non-negative integer. The maximum value and the 64th power of 2. With this physical concept, lsn can easily be converted to the offset of the current log.

II. Understanding of checkpoints in innodb

Here I only discuss checkpoints under normal operation. Incremental checkpoints similar to oracle in innodb. The normal operation of checkpoint is triggered by the master thread. We know that dirty data is constantly written to disk through page clean thread and lru manager thread, so we must know a recovery starting point when carrying out an abnormal restart, but this starting point cannot be recorded in memory and must be solidified to disk. When recovering, read the redo after this point to recover, and checkpoint is to complete this thing. The following is the checkpoint execution process.

Normally, the master thread will checkpoint every second. Its purpose is (see the log_checkpoint function):

Check to see if there is any dirty data written since the last checkpoint.

If so, MLOG_FILE_NAME is written for each modified file in redo, and a total MLOG_CHECKPOINT is written when it is finished (see the fil_names_clear function). 1. MLOG_FILE_NAME mainly records the data files that have changed since the last checkpoint. 2. MLOG_CHECKPOINT mainly records the lsn of the checkpoint. This step iterates through fil_system- > named_spaces to find out if there is a linked list of files that have been modified since the last checkpoint.

If so, write the corresponding checkpoint information contained in the redo log header (asynchronous write).

In fact, we can understand that a checkpoint is a checkpoint in which a masterthread wakes up every second to see where the dirty data has been written, and then records it in the right place for carsh recovery to use. (see srv_master_thread function)

3. Checkpoint information in show engine innodb

The following is the information for a library without any update operations as follows:

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

Log sequence number: lsn that has been written to log buffer. Refer to the mtr_t::Command::finish_write function.

Log flushed up to: the lsn of the redo that has been written to the log file. Refer to the log_write_flush_to_disk_low function.

Pages flushed up to: all the dirty data before this lsn has been written to the data file. Refer to the log_buf_pool_get_oldest_modification function.

Last checkpoint at: where the last checkpoint was recorded. Refer to the next_checkpoint_lsn function.

Here is the source code for this output:

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)

Generally speaking, Log sequence number > Log flushed up to > Pages flushed up to > Last checkpoint at, but note here. Pages flushed up to 697794162 and Last checkpoint at 697794153, obviously this is a library with no operations, so Pages flushed up to should be equal to Last checkpoint at, but there is a difference, the difference is:

697794162-697794153 = 9

This happens to be the length of MLOG_CHECKPOINT. The source code snippet is as follows:

Oldest_lsn last_checkpoint_lsn + SIZE_OF_MLOG_CHECKPOINT / * * Size of a MLOG_CHECKPOINT record in bytes.The record consists of a MLOG_CHECKPOINT byte followed bymach_write_to_8 (checkpoint_lsn) * / # define SIZE_OF_MLOG_CHECKPOINT 9 IV. Timing of several kinds of redo written by our debug

The master thread invokes the stack frame per second (it could be idle, it could be active and it has something to do with detecting whether cache merging is needed)

# 0log _ 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 thread checkpoint calls per second (could be idle or active related to detecting whether cache merging is needed)

# 0log _ group_write_buf (group=0x33f29f8, buf=0x7fffa5a38000 "\ 200024\ 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 thread call stack frame

# 0log _ group_write_buf (group=0x33f29f8, buf=0x7fffa5a38000 "\ 200024\ 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

The current thread commit call stack frame is as follows:

# 0log _ group_write_buf (group=0x33f29f8, buf=0x7fffa5a38000 "\ 200024\ 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, 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 (not debug)

Insufficient redo buffer (not debug)

The above is all the content of this article "Innodb checkpoint and redo writing time". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.