Introduction to Mysql Log

Introduction to Mysql Log

[TOC]

reference:

difference between redo log and undo log

understands the InnoDB storage engine

Preface

The knowledge involved is Buffer Pool & Redo Log & Undo Log & BinLog & CheckPoint & Dirty Pages.

If each data modification operation is directly operated on the disk, when the number of operations increases, then these operations will become very slow. In order to improve the concurrency between operations, Mysql designed a caching system.

One, Buffer Pool

Buffer pool. When the data is changed, it will not be synchronized to the disk immediately, but will be placed in the buffer pool first.

2. Redo Log & Undo Log

2.1 Redo Log

In order to prevent the data in the buffer pool from being lost after the system is powered off, that is, guarantees the durability of the transaction , a log after data modification is hereby recorded. For example, if the transaction changes a from 1 -> 2 and b from 4 -> 3, then Log will record a:2, b:3.

2.1.1 Features

1. The size is fixed, you can set innodb_log_write_ahead_size by command;
2. After it is full, continue to write again, overwriting the content previously written;

2.1.2 CHECKPOINT

The function of CHECKPOINT is to record the position where the last flashing to the disk ended, so that there is no need to flash the entire redo log every time.

dirty page : The part of the redo log that is different from the disk data is called the dirty page, that is, the data contained in the CHECKPOINT to the cursor position of this file.

2.1.3 Purpose

1. Solve the problem of operating the disk immediately after data changes, improve the speed, and then modify the data of the redo log according to the strategy;
2. When the system is powered off, use redo log to recover data;

2.1.4 Flashing Redo Log to Disk

Update the disk data according to the data changes recorded in the redo log. All redo log flashing to disk mentioned in this article refers to flashing data from CHECKPOINT to the end of the current file cursor.

brush in timing :

1. Brush in regularly, and brush in the redo log at regular intervals;
2. The lru (least recently used) algorithm is flushed in. When the free space of the Buffer Pool is insufficient, the LRU algorithm is used to eliminate the cache records. Because the cache is missing, the query will hit the disk. In order to ensure data consistency, the redo log must be flushed to the disk;
3. The log cannot be flushed. When the log to be overwritten has not been flushed to the disk, flush the redo log to the disk;

2.2 Undo Log

In order to enable the transaction to be rolled back to the previous state during execution, that is, guarantees the atomicity of the transaction , hereby records a log before the data modification. For example, if the transaction changes a from 1 -> 2 and b from 4 -> 3, then Log will record a:1, b:4.

2.2.1 Features

1. When the log is full, a new file is created;
2. When the total amount of files is too large, delete old files;

2.2.2 Purpose

1. Used for transaction rollback;
2. Realize MVCC (multi-version concurrency control);

2.3 Recording steps

Suppose there are two data A and B, the values are 1, 2 respectively.

1. The transaction begins.
2. Record A=1 to undo log.
3. Modify A=3.
4. Record A=3 to redo log.
5. Record B=2 to undo log.
6. Modify B=4.
7. Record B=4 to redo log.
8. Write undo log to disk.
9. Write redo log to disk.
10. The transaction is committed.

Three, Binlog

Binlog only records 's attempt to change the data , even if the data does not change before and after the update, it will be recorded.

There are two recording methods:

1. Only record sql statement;
2. Record the content of the data line before and after the execution of the statement;