InnoDB internals - checkpoint, LSN, dirty pages

Question

I'm looking into InnoDB internals regarding buffer pool, checkpoint and dirtypage. However there isn't enough information to answer my question.

1. When I run show global status, I can get Innodb_buffer_pool_pages_dirty and Innodb_buffer_pool_bytes_dirty.

I want to know where this information comes from. Does any memory (such as buffer pool or information schema) keep this information and just show them when a query comes? Or does a logical object calculate them whenever a query comes? If so, what is the basic information which could be the materials to calculate?

2. When I run SHOW ENGINE INNODB STATUS\G, mysql shows the following information:

   LOG
   ---
   Log sequence number 9682004056
   Log flushed up to   9682004056
   Last checkpoint at  9682002296
   

I also want to know where this information comes from. Especially the "Last checkpoint at". As I researched, the log sequence number is related to the "Log Block Header Number" of InnoDB log blocks in the redo log files. Is this right? Then how does MySQL convert it to a log sequence number and how does it get the base information for them?

Answer 1

1. Your answers are on the source code: innodb_buffer_pool_pages_dirty. Most of these variables are maintained on memory directly or through very simple calculations. There is no danger of durability loss, as the ones that are "essential" for innodb to work can be recalculated from the transaction log or are 0 on start.

2. Not really, the lsn is just an offset in bytes, it is maintained on memory (log_sys->lsn) and in the case of a failure, it can be recovered from the header of the transaction log (plus pure file offsets). They are changed on commit/log flush/tablespace flush, both on memory and on disk (exact method depends on the durability configuration, buffering type, etc.).

For basic InnoDB stuff, you can check this blog post by the Oracle InnoDB team and/or check the source code for InnoDB.