My05_mysql Check Point Summary

A brief description of MySQL checkpoint is helpful for understanding MySQL database recovery.

Database version

Mysql> Selectversion ();+-----------+|Version ()|+-----------+| 8.0. One    |+-----------+1Rowinch Set(0.00Sec

Check Point view

Mysql>show engine InnoDB status\g;---LOG---LogSequence Number          25048841LogBuffer assigned up to    25048841LogBuffer completed up to   25048841LogWritten up to            25048841LogFlushed up to            25048841Added dirty pages up to      25048841Pages flushed up to          25048841 LastCheckpointAt25048841 * LogI/O's done, 0.00 log I/O'S/Second----------------------

Last checkpoint is the checkpoint location, and when the database is not busy, you can see that the location of the Log sequence Number,log flushed,pages flushed is close or equal. When database DML operations are large, their difference is larger.

Related parameters

When the MySQL database flushes the dirty pages in memory to disk, the checkpoint is logged once, and this is the checkpoint function that flushes the memory dirty pages to the disk. InnoDB automatically refreshes dirty pages per second or a few seconds, but it is also affected by the following two parameters

Mysql>Show variables like '%max_dirty_pages%';+--------------------------------+-----------+|Variable_name|Value|+--------------------------------+-----------+|innodb_max_dirty_pages_pct| 50.000000 ||Innodb_max_dirty_pages_pct_lwm| 10.000000 |+--------------------------------+-----------+2Rowsinch Set(0.00Sec

When the InnoDB dirty page usage exceeds the value of INNODB_MAX_DIRTY_PAGES_PCT_LWM, in addition to the auto-refresh dirty page mechanism of the InnoDB itself, the Brush dirty page will start to accelerate when dirty page usage exceeds Innodb_max_dirty_pages_ The value of the PCT will increase the speed at which dirty pages are flushed again.

A innodb_max_dirty_pages_pct_lwm of 0 indicates that the parameter does not take effect.

Check Point classification

Sharp Checkpoint (Full check point)

Writing all of the dirty pages in memory to disk is a full checkpoint, such as when the DB instance is closed.

Fuzzy Checkpoint (Blur Check Point)
Flushing a portion of a dirty page to disk, which is a fuzzy checkpoint, is basically the type of checkpoint that is generated by the database instance's running process.

Check Point function

The primary role is to reduce data recovery time. Because the data before the checkpoint location is written to disk, data recovery does not need to pay attention to this part of the data, so it reduces the time to recover.

Often said crash crash recovery, what data is recovered? Restores the data between the last checkpoint to log flushedup. Log Flushedup is the location of the memory write log and the most recent location of the log file. The new words go into memory, and the data that has not been written to the log file is not recoverable.

My05_mysql Check Point Summary