Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ MySQL Tutorial: About checkpoint mechanism     - Puppet 3.x installed on Debian 7 (Server)

- Use the top command (Linux)

- Protobuf compiled and used on the Ubuntu 14.04 (Programming)

- How to Install Cantata MPD 1.3.3 for Ubuntu and Derived Version Users (Linux)

- Ubuntu and derived versions of the user how to install G Mic (Linux)

- Install Oracle JDK 8 and JVM class loading mechanism in Linux (Linux)

- Win7 + Ubuntu Kylin + CentOS 6.5 installed three systems (Linux)

- CentOS 6.5 install Firefox (Linux)

- Linux Open coredump (Linux)

- CentOS7 + Redis Live Installation and Configuration (Linux)

- systemd run levels and service management command Introduction (Linux)

- Linux System Getting Started Learning: the Linux Wireshark interface dead solve (Linux)

- Subquery Oracle study notes (Database)

- Install DB2 V10 and Data Studio V3 under Linux (Ubuntu) environment (Database)

- To protect the temporary files for Linux security (Linux)

- Android will save the picture to see the album and timely (Programming)

- Binary Tree Traversal (Linux)

- Linux basic introductory tutorial ---- regex basis (Linux)

- To install file manager Nautilus 3.12.2 under ubuntu (Linux)

- sqlplus connect to Oracle (Database)

  MySQL Tutorial: About checkpoint mechanism
  Add Date : 2018-11-21      
  One. Introduction

Consider this scenario: If the redo log can be infinitely increased, while the pool is large enough, you do not need the new version of the buffer pool page refresh back to disk. Because when downtime occurs entirely possible to recover data in the entire database system downtime occurs through time to redo logs.

But this requires two preconditions: 1, the database can be cached in the buffer pool all the data; 2, redo log can grow indefinitely

So Checkpoint (Checkpoint) technology was born, in order to address the following questions: 1, shorten the recovery time of the database; 2, the buffer pool is not enough, the dirty pages are flushed to disk; 3, redo log is unavailable, refresh dirty pages.

When the database downtime occurs, the database does not need to redo all of the log, because Checkpoint previous page have been refreshed back to disk. Just after the database redo log on Checkpoint recovery, thus greatly reducing the recovery time.
When the pool is not enough, according to the LRU algorithm overflow least recently used page, if this page is dirty pages, then you need to enforce Checkpoint, dirty page is a new version of the page to brush back to disk.
When redo logs appear unavailable, because the current system transaction database redo logs are designed to be recycled, and not allowed to grow indefinitely, the redo logs can be reused part is no longer refers to the redo log longer needed, when the database downtime occurs, database recovery operations do not need this part of the redo log, so this part can be covered reused. If you need to use the redo logs, you must force Checkpoint, refresh the page buffer pool at least to the current position of the redo logs.
For InnoDB storage engine, by LSN (Log Sequence Number) to mark the release.

LSN is an 8-byte number, each page contains LSN, redo logs also LSN, Checkpoint also LSN. You can command SHOW ENGINE INNODB STATUS observed:

mysql> show engine innodb status \ G




Log sequence number 34778380870

Log flushed up to 34778380870

Last checkpoint at 34778380870

0 pending log writes, 0 pending chkp writes

54020151 log i / o's done, 0.92 log i / o's / second

Checkpoint time occurs, the selection criteria and dirty pages are all very complicated. The Checkpoint doing things is nothing less than the dirty pages in the buffer pool back to disk brush, except that the number of each page refresh to disk, from where to get dirty every page, and what time-triggered Checkpoint.

Two, Checkpoint classification

Inside InnoDB storage engine, there are two Checkpoint, respectively: Sharp Checkpoint, Fuzzy Checkpoint

Sharp Checkpoint occurs when the database is closed all the dirty pages flushed to disk, which is the default operating mode, ie parameter innodb_fast_shutdown = 1. However, if the database is also used at runtime Sharp Checkpoint, then the availability of the database will be greatly affected. Therefore, within the InnoDB storage engine Fuzzy Checkpoint refresh the page, that is only part of the dirty pages to refresh, rather than flush all dirty pages to disk.

Fuzzy Checkpoint: 1, Master Thread Checkpoint; 2, FLUSH_LRU_LIST Checkpoint; 3, Async / Sync Flush Checkpoint; 4, Dirty Page too much Checkpoint

1, Master Thread Checkpoint

Per second speed refresh every ten seconds or a certain percentage of dirty pages from the buffer pool in the page list back to disk, this process is asynchronous, then the InnoDB storage engine can perform other operations, the thread does not block the user's query.

2, FLUSH_LRU_LIST Checkpoint

Because InnoDB storage engine needs to ensure LRU list needs to have almost 100 free pages available. Before InnoDB1.1.x version, check the LRU list if there is enough free space operation occurs in the user query thread, obviously this will block the user's query. If there is no available free page 100, the InnoDB storage engine will remove the tail end of the LRU list page. If these pages have dirty pages, then the need for Checkpoint, and these pages are from the LRU list, so called FLUSH_LRU_LIST Checkpoint.

From MySQL 5.6 version, which is InnoDB1.2.x version, this check is placed in a separate thread Page Cleaner carried out and the user can LRU list the number of pages available by innodb_lru_scan_depth control parameter, this value defaults 1024, such as:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_lru_scan_depth';

+ ----------------------- + ------- +

| Variable_name | Value |

+ ----------------------- + ------- +

| Innodb_lru_scan_depth | 1024 |

+ ----------------------- + ------- +

3, Async / Sync Flush Checkpoint

Refers to the case of the redo log file is unavailable, then you need to force some of the pages are flushed to disk, but this time dirty pages are selected from the dirty pages list. If that has been written to the redo log LSN recorded as redo_lsn, will have been flushed to disk latest page LSN recorded as checkpoint_lsn, can be defined:

checkpoint_age = redo_lsn - checkpoint_lsn

And then define the following variables:

async_water_mark = 75% * total_redo_log_file_size

sync_water_mark = 90% * total_redo_log_file_size

If each redo log file size is 1GB, and defines two redo log files, the total size of the redo log file is 2GB. So async_water_mark = 1.5GB, sync_water_mark = 1.8GB. then:

When checkpoint_age
Fires when async_water_mark
checkpoint_age> sync_water_mark this situation rarely occurs, redo log files unless you set too small, and in doing similar LOAD DATA BULK INSERT operations. At this trigger Sync Flush operation, refresh enough dirty pages from Flush list back to disk, so that refresh meet checkpoint_age
Visible, Async / Sync Flush Checkpoint is to ensure the availability of redo logs recycled. Before InnoDB 1.2.x version, Async Flush Checkpoint clog find user queries threading issues, and Sync Flush Checkpoint will block all user queries thread, and wait for the dirty page refresh is complete. InnoDB 1.2.x version from the start - that is, MySQL 5.6 version, this part of the refresh operation similarly placed into separate Page Cleaner Thread, and it does not block the user's query thread.

The official version of MySQL and can not view or refresh the page is performed Checkpoint from the LRU list Flush from the list, do not know Async / Sync because redo logs generated Flush of times. But InnoSQL version provides a method, you can command SHOW ENGINE INNODB STATUS to observe, such as:

mysql> show engine innodb status \ G



Total memory allocated 2058485760; in additional pool allocated 0

Dictionary memory allocated 913470

Buffer pool size 122879

Free buffers 79668

Database pages 41957

Old database pages 15468

Modified db pages 0

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 15032929, not young 0

0.00 youngs / s, 0.00 non-youngs / s

Pages read 15075936, created 366872, written 36656423

0.00 reads / s, 0.00 creates / s, 0.90 writes / s

Buffer pool hit rate 1000/1000, young-making rate 0/1000 not 0/1000

Pages read ahead 0.00 / s, evicted without access 0.00 / s, Random read ahead 0.00 / s

LRU len: 41957, unzip_LRU len: 0

I / O sum [39]: cur [0], unzip sum [0]: cur [0]

4, Dirty Page too much

That is too many dirty pages, resulting in InnoDB storage engine to force Checkpoint. Its general purpose is to ensure there is enough available buffer pool page. Innodb_max_dirty_pages_pct control parameters which may be:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_max_dirty_pages_pct';

+ ---------------------------- + ------- +

| Variable_name | Value |

+ ---------------------------- + ------- +

| Innodb_max_dirty_pages_pct | 75 |

+ ---------------------------- + ------- +

innodb_max_dirty_pages_pct is 75, said that when the number of dirty pages in the buffer pool to occupy 75%, the mandatory Checkpoint, to refresh a portion of dirty pages to disk. Before InnoDB 1.0.x version, this parameter defaults to 90, after 75 versions.

Three, Checkpoint mechanism

In Innodb transaction log, using Fuzzy Checkpoint, Innodb each taking the oldest modified page (last checkpoint) corresponding to the LSN, then this LSN dirty pages as Checkpoint point to a log file, which means that before "in this LSN LSN corresponding log data and have to redo log flush

When mysql crash when, Innodb scan redo log, beginning from the last checkpoint to apply redo log buffer pool, until the last checkpoint corresponding LSN equal Log flushed up to the corresponding LSN, the recovery is complete

So specifically how to restore it?

Innodb transaction log of a total through four stages:

Create stages: Create a transaction log;
Log brush plate: log is written to a log file on disk;
Data brush plate: dirty pages corresponding to the log data is written to the data file on disk;
Write CKP: logs are written to the log file as Checkpoint;

This corresponds to four phases, the system records the four log-related information for a variety of other processing uses:

Log sequence number (LSN1): maximum current system LSN, LSN new transaction log will be generated (LSN1 + size of the new log) on ​​this basis;
Log flushed up to (LSN2): Current LSN has been written to the log file;
Oldest modified data log (LSN3): The current oldest dirty page data corresponding to the LSN, Checkpoint write directly when this LSN written to the log file;
Last checkpoint at (LSN4): the current has been written Checkpoint LSN;
For the system, more than four LSN is decreasing, namely: LSN1> = LSN2> = LSN3> = LSN4.

Specific examples are as follows (using the show innodb status \ G command, Oldest modified data log is not shown):



Log sequence number 34822137537

Log flushed up to 34822137537

Last checkpoint at 34822133028

0 pending log writes, 0 pending chkp writes

54189288 log i / o's done, 3.00 log i / o's / second

Fourth, log protection mechanisms

mysql crash when, Innodb brush plate has a log mechanism, can be controlled by innodb_flush_log_at_trx_commit parameters, it said here is how to prevent the loss of logs covered cause the log

Innodb the checkpoint and redo log what a close relationship? There are a few nouns need to explain:

Ckp age (dynamic movement): The oldest dirty page has not flush the data files that do not have the range last checkpoint
Buf age (dynamic movement): modified page information is not written in the log, but the log buffer has been
Buf async (fixed point): log space size 7/8, when buf age moved to Buf async point, forced to not write the log of the modified page information is written to the log in the beginning, do not block the transaction
Buf sync (fixed point): log space size 15/16, when writing a great, buf age move very fast, all of a sudden to buf sync point, blocking the transaction, forced to start writing the modified page information to log in . If you do not block the transaction, redo log exists without making last checkpoint covering dangerous
Ckp async (fixed point): log space size 31/32, when ckp age arrives ckp async, forced to do last checkpoint, not blocking transaction
Ckp sync (fixed point): log space size, when the risk of redo log covered ckp age arrives ckp sync, forced to do last checkpoint, blocking the transaction, there is a
Continued analysis of four cases

In buf async and buf sync between if buf age
If buf age after buf sync (Of course, this situation does not exist, mysql has a protective mechanism)
If ckp age between ckp async and ckp sync (this case is not present)
If ckp age after ckp sync (this case is not present)
The first case:

When the huge amount written, buf age to move between buf async and buf sync, trigger write to log in, mysql to log to write as much, if slow the write amount, buf age and move back to "FIG. a "state. If the write speed of greater than flush log, buf age will eventually overlap and buf sync, then all transactions are blocked, forcing 2 * (Buf age-Buf async) brush plate of dirty pages, then it would be more IO busy.

Second case:

Of course, this situation is impossible, because if there is, redo log coverage may exist, data will be lost. buf age will cross the log size, the size of buf age may be more than log size, if you want to brush buf age, then the entire log size enough to accommodate all of buf age.

The third and fourth absence of analysis:

ckp age buf age is always at the back (left), because ckp age is the last checkpoint point, always catch buf age (as much of the modified page flush to disk), so buf age must be reached first buf sync.

ckp async ckp sync and the meaning of existence?

mysql also present in the page cache high water and low water, when the dirty page touched the low water, os is the beginning of flush dirty page to disk, to the high water, will block all movement, os will be crazy flush dirty page, disks busy existence IO Storm,
- To install OwnCloud 7.0.4 under Ubuntu (Linux)
- Linux Apache server security (Linux)
- Android View event delivery (Programming)
- How to install Linux Kernel 4.4 on Ubuntu (Linux)
- Use NTFS-3G to mount NTFS partitions under Linux U disk and removable hard disk (Linux)
- Use libpq under Ubuntu 14.04 (Linux)
- xargs Detailed description (Linux)
- tar command to extract a file error: stdin has more than one entry (Linux)
- OpenGL Superb Learning Notes - Depth Texture and Shadows (Programming)
- PULL operation mechanism parsing XML Comments (Programming)
- To delete the directory and all specified files under the Mac (Linux)
- Oracle 12c detailing the new features (Database)
- CentOS 6.4 dial-up Raiders (Linux)
- Simple steps allows you to build a more secure Linux server (Linux)
- Hadoop 2.6.0 stand-alone / pseudo-distributed installation (Server)
- Hadoop - Task Scheduling System Comparison (Server)
- using the ssh command to check the socket / Network Connections (Linux)
- CentOS 6.6 x64 Oracle Database 11gR2 RAC automated installation scripts (Database)
- Nginx version information hidden or modified (Server)
- Linux uses shared memory communication process synchronization Withdrawal (Programming)
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.