Shulou(Shulou.com)06/01 Report--

1) preface and questions:

Let's look at the screenshot first.

Log sequence number-the serial number generated by the log in memory

Log flushed up to-the value (in bytes) that is brushed into the redo log

Pages flushed up to-- that's what I'm going to talk about, which I'll talk about in more detail later.

Last checkpoint at-location of the last checkpoint

What on earth is Pages flushed up to here? And when the database does not write data, always Pages flushed up to is not equal to Last checkpoint at, and why is that, and Pages flushed up to-Last checkpoint at=9 (here you can take a closer look)?

I have checked the information on the Internet and asked some bosses, and the answers are really varied.

Among them, there are two main answers, one is, as the name implies, the LSN; to which the dirty page is refreshed, and the other is: new_modification LSN

2) Analysis:

For the first kind, it is obviously not true to say that dirty pages are refreshed to disk lsn. In fact, the checkpoint means that the data before LSN has been dropped. Here, the data of the checkpoint contains the data of dirty pages. In the actual testing process, the Pages flushed up to is obviously larger than the LSN of the checkpoint, and after the checkpoint, there is no guarantee that all the data is off the disk, so if Pages flushed up to is the lsn that the dirty pages are refreshed to the disk, then there will be a situation, the dirty pages of lsn1=3000 are flushed (pages flushed up to is 3000), and the dirty pages of lsn=2800 are not flushed, do you think this value is meaningful?

For the second kind: the new modification lsn of the page that is said to be checkpointed by flush list (that is, a memory page containing two lsn,flush list is sorted according to the lsn of the first modification of the page, as long as a page is modified to add flush list, and new modification lsn is written how many bytes of data, new modification lsn will add how many bytes of data); why the database finally did not make changes, Pages flushed up to is not equal to Last checkpoint at? Their answer is that a page has been modified many times.

For greater clarity, I posted these data structures:

Every buffer pool contains flush list

Struct buf_pool_t {

...

UT_LIST_BASE_NODE_T (buf_page_t) flush_list

UT_LIST_BASE_NODE_T (buf_page_t) free

...

}

Class buf_page_t {

Public:

...

/ / this value is that lsn will increase as long as it is modified. If a page is not modified in memory, it will always be 0 and flush list will not be added.

Lsn_t newest_modification

/ *!

< log sequence number of the youngest modification to this block, zero if not modified. Protected by block mutex */ //这个值，只要当前页被刷入了磁盘，他的值就会置为0，第一次对页的修改，就会加入flush list，第二次对该页修改，此值不会增加，因为flush list 是推进检查点的。 lsn_t oldest_modification; /*!< log sequence number of the START of the log entry written of the oldest modification to this block which has not yet been flushed on disk; zero if all modifications are on disk. Writes to this field must be covered by both block->

< in: file where to print */ { double time_elapsed; time_t current_time; log_mutex_enter(); 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)

...

}

② view log_buf_pool_get_oldest_modification source code

Static lsn_t

Log_buf_pool_get_oldest_modification (void)

/ * /

{

Lsn_t lsn

Ut_ad (log_mutex_own ())

Lsn = buf_pool_get_oldest_modification ()

/ / if the LSN returned by buf_pool_get_oldest_modification is 0 (the implication here is that oldest lsn is 0, that is, all the data is brushed to disk), then take log_sys- > lsn.

If (! lsn) {

Lsn = log_sys- > lsn

}

Return (lsn)

}

From this function, we can see that if all the dirty pages in buffer pool are brushed to disk (and no data is written at this time), then the largest LSN (log_sys- > lsn) is taken, that is, the Log sequence number displayed by show engineinnodb status.

③ but we still don't know how to get oldest modification lsn here, so we need to look at the source code of buf_pool_get_oldest_modification:

Buf_pool_get_oldest_modification (void)

/ * /

{

Lsn_t lsn = 0

Lsn_t oldest_lsn = 0

/ * When we traverse all the flush lists we don't want another

Thread to add a dirty page to any flush list. , /

Log_flush_order_mutex_enter ()

/ / traverse all buffer pool

For (ulint I = 0; I

< srv_buf_pool_instances; i++) { buf_pool_t* buf_pool; buf_pool = buf_pool_from_array(i); buf_flush_list_mutex_enter(buf_pool); buf_page_t* bpage; /* We don't let log-checkpoint halt because pages from system temporary are not yet flushed to the disk. Anyway, object residing in system temporary doesn't generate REDO logging. */ //从这里我们可以看出bpage是取flush list中最大的oldest modifcation LSN(注意这里的逻辑，如果是系统临时表空间，就不应该算在内) for (bpage = UT_LIST_GET_LAST(buf_pool->

Flush_list)

Bpage! = NULL

& & fsp_is_system_temporary (bpage- > id.space ())

Bpage = UT_LIST_GET_PREV (list, bpage)) {

/ * Do nothing. , /

}

If (bpage! = NULL) {

Ut_ad (bpage- > in_flush_list)

Lsn = bpage- > oldest_modification

}

Buf_flush_list_mutex_exit (buf_pool)

/ / above is the page that fetches the largest oldest modication lsn from the flush list in buffer pool, while here is the page that takes the smallest oldest modification lsn from the maximum oldest modification lsn of each buffer pool.

If (! oldest_lsn | | oldest_lsn > lsn) {

Oldest_lsn = lsn

}

}

Log_flush_order_mutex_exit ()

/ * The returned answer may be out of date: the flush_list can

Change after the mutex has been released. , /

Return (oldest_lsn)

}

④ here, our whole process is clear. Pages flushed up to takes the value with the smallest oldest modification lsn among the largest oldest modification lsn pages of all buffer pool. If the oldest modification lsn you go to is 0, which means there are no dirty pages, then we take the value of log_sys- > lsn, that is, the Log sequence number displayed by show engineinnodb status.

For example: our buffer pool is set to 4; for the first buffer pool, we take the maximum value of page oldest modification lsn in flush list to 100

For the second buffer pool, we take the maximum value of page band oldest modification lsn in flush list to 140; for the third buffer pool, we take the maximum value of page band oldest modification lsn in flush list to 98; for the fourth buffer pool, we take the maximum value of page band oldest modification lsn in flush list to 130; then the pages flushed up to we get is 98

⑤, we know where this Pages flushed up to gets the value, but now I don't know the meaning of this value.

Let's look at a function.

/ * Make a checkpoint. Note that this function does not flush dirty

Blocks from the buffer pool: it only checks what is lsn of the oldest

Modification in the pool, and writes information about the lsn in

Log files. Use log_make_checkpoint_at () to flush also the pool.

* * /

/ / from the comments of this function, we can clearly know that this function is used to do checkpoints.

Log_checkpoint (

Bool sync

Bool write_always)

{

Lsn_t oldest_lsn

Ut_ad (! srv_read_only_mode)

If (recv_recovery_is_on ()) {

Recv_apply_hashed_log_recs (TRUE)

}

# ifndef _ WIN32

Switch (srv_unix_file_flush_method) {

Case SRV_UNIX_NOSYNC:

Break

Case SRV_UNIX_O_DSYNC:

Case SRV_UNIX_FSYNC:

Case SRV_UNIX_LITTLESYNC:

Case SRV_UNIX_O_DIRECT:

Case SRV_UNIX_O_DIRECT_NO_FSYNC:

Fil_flush_file_spaces (FIL_TYPE_TABLESPACE)

}

# endif / *! _ WIN32 * /

Log_mutex_enter ()

Ut_ad (! recv_no_log_write)

/ / obtain oldest LSN

Oldest_lsn = log_buf_pool_get_oldest_modification ()

/ * Because log also contains headers and dummy log records

Log_buf_pool_get_oldest_modification () will return log_sys- > lsn

If the buffer pool contains no dirty buffers.

We must make sure that the log is flushed up to that lsn.

If there are dirty buffers in the buffer pool, then our

Write-ahead-logging algorithm ensures that the log has been

Flushed up to oldest_lsn. , /

/ / from here we can see why last checkpoint lsn is not equal to pages flushed up to, because when mysql makes a checkpoint, we have to type the MLOG_CHECKPOINT redo log, and this MLOG_CHECKPOINT takes up exactly 9 bytes, so there is pages flushed up to-last checkpoint lsn=9 after all the dirty pages are brushed.

Ut_ad (oldest_lsn > = log_sys- > last_checkpoint_lsn)

If (! write_always)

& & oldest_lsn

Last_checkpoint_lsn + SIZE_OF_MLOG_CHECKPOINT) {

/ * Do nothing, because nothing was logged (other than

A MLOG_CHECKPOINT marker) since the previous checkpoint. , /

Log_mutex_exit ()

Return (true)

}

Lsn_t flush_lsn = oldest_lsn

Const bool do_write

= srv_shutdown_state = = SRV_SHUTDOWN_NONE

| | flush_lsn! = log_sys- > lsn |

If (fil_names_clear (flush_lsn, do_write)) {

Ut_ad (log_sys- > lsn > = flush_lsn + SIZE_OF_MLOG_CHECKPOINT)

Flush_lsn = log_sys- > lsn

}

Log_mutex_exit ()

Log_write_up_to (flush_lsn, true)

DBUG_EXECUTE_IF (

"using_wa_checkpoint_middle"

If (write_always) {

DEBUG_SYNC_C ("wa_checkpoint_middle")

Const my_bool b = TRUE

Buf_flush_page_cleaner_disabled_debug_update (

NULL, & b)

Dict_stats_disabled_debug_update (

NULL, & b)

Srv_master_thread_disabled_debug_update (

NULL, & b)

});

Log_mutex_enter ()

Ut_ad (log_sys- > flushed_to_disk_lsn > = flush_lsn)

Ut_ad (flush_lsn > = oldest_lsn)

/ / the checkpoint greater than or equal to oldest_lsn indicates that the disk has been flushed

If (log_sys- > last_checkpoint_lsn > = oldest_lsn) {

Log_mutex_exit ()

Return (true)

}

If (log_sys- > n_pending_checkpoint_writes > 0) {

/ * A checkpoint write is running * /

Log_mutex_exit ()

If (sync) {

/ * Wait for the checkpoint write to complete * /

Rw_lock_s_lock (& log_sys- > checkpoint_lock)

Rw_lock_s_unlock (& log_sys- > checkpoint_lock)

}

Return (false)

}

/ / the location of the next LSN

Log_sys- > next_checkpoint_lsn = oldest_lsn

Log_write_checkpoint_info (sync)

Ut_ad (! log_mutex_own ()

Return (true)

}

4) Summary:

Pages flushed up to refers to the location where the next checkpoint lsn will be done; in the absence of new data writes

Pages flushed up to takes Log sequence number (log_sys- > lsn); why is last checkpoint point not equal to pages flushed up to when there is no data to write? Because when you do checkpoint, the redo log will write MLOG_CHECKPOINT, and MLOG_CHECKPOINT takes up nine bytes, so pages flushed up to-last checkpoint point=9 will appear.

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.