MySQL checkpoint and LSN detailed

Checkpoin is the redo log checkpoint that refreshes the data page to disk, saves the record with the LSN number, the function is when the outage and other crash situation, when the restart will be queried checkpoint point, after the checkpoint occurs after the changes to disk recovery.

Mysql> show engine InnoDB status\g;

---

LOG

---

Log Sequence Number 10623965866

Log flushed up to 10623965866

Pages flushed up to 10623965866

Last checkpoint at 10623965857

0 Pending log flushes, 0 pending CHKP writes

Log I/O ' s done, 0.81 log I/O ' S/second

Last checkpoint at is the system that finally refreshes the page data to disk in buffer pool checkpoint,checkpoint is associated with redo log and is recorded in the Redo log. Checkpoint records the header of the first file in the redo log, storing two values in a circular replacement modification.

LSN (log sequence number), 5.6.3 8 bytes, the LSN is used primarily for crash when the data is recovery,lsn is a continuously incrementing integer number representing the total amount of bytes that the transaction writes to the log.

LSN not only exists in the redo log, but also has a corresponding LSN number at the head of each data page, which records the LSN number of the last modification of the current page and is used to make a log LSN number for the specific gravity at recovery to determine whether to restore the page. The checkpoint mentioned earlier is also recorded with the LSN number, and the LSN number is a concatenation of the start of a transaction to the recovery process.

---

LOG

---

Log Sequence Number 10623965866

Log flushed up to 10623965866

Pages flushed up to 10623965866

Last checkpoint at 10623965857

0 Pending log flushes, 0 pending CHKP writes

Log I/O ' s done, 0.81 log I/O ' S/second

Again with the above results, log sequence number is the current generation of the latest log sequence numbers

Log flushed up to: the LSN number that has been flushed to the Redo log disk file

Pages flushed up to: This entry is 5.6 new, this official website did not introduce, but before checkpoint, personally understood as already in Buffer poll write to the data dirty page LSN number

In the production environment often last checkpoint will lag log sequence number many, Checkpoint record data page refresh situation, and data page refresh is the MySQL internal mechanism decision, so there will be a difference, In order to ensure that the database crash restart data is not lost, we need to redo log Refresh configuration item Innodb_flush_sync configured to 1, so that redo log at the end of the transaction is flushed to disk to use redo log for data recovery. Therefore, the LSN of log flushed up will be the same as the log sequence number.

This article is from the "Yuanzhan" blog, make sure to keep this source http://xiaozhong991.blog.51cto.com/2354914/1760379

MySQL checkpoint and LSN detailed