Mysql Notes (i)

InnoDB Storage Engine

mysql存储引擎（好难用，看https://www.zybuluo.com/eqyun/note/27850）

Brief introduction

InnoDB is a transaction-safe MySQL storage engine, starting from the MySQL5.5 version is the default table storage engine, is the first fully supported ACID事务 MySQL storage engine, characterized by a row lock design, support MVCC, support foreign keys, provide consistent lock read, Designed to make the most efficient use of memory and CPU

InnoDB Storage Engine Architecture

后台线程(多个)->InnoDB存储引擎内存池->物理文件

Background thread 1. Master Thread

The core background thread is responsible for asynchronously flushing data from the buffer pool to disk, ensuring data consistency, including dirty page refreshes, merge insert buffers (insert buffer), recovery of undo pages, etc.

2. IO Thread

AIO (Async IO) is used extensively in InnoDB to process IO requests, and IO thread is primarily responsible for callback processing of these IO requests. There are 4 types of Io thread: write , read ,, insert buffer and log IO thread . At the beginning of the InnoDB 1.0.x version, the read thread and the write thread were increased by 4, respectively, with innodb_file_io_threads innodb_write_io_threads parameters to set, as
You can see that thread 0 is insert buffer thread,io thread 1 is log thread.

Mysql>show engine InnoDB status\g; ***************************1. Row ***************************type:innodb Name: ....--------FILE I/O--------I/O Thread0State:waiting forcompleted AIO requests (insert buffer thread) I/O Thread1State:waiting forcompleted AIO requests (log thread) I/O Thread2State:waiting forcompleted AIO requests (read thread) I/O Thread3State:waiting forcompleted AIO requests (read thread) I/O Thread4State:waiting forcompleted AIO requests (read thread) I/O Thread5State:waiting forcompleted AIO requests (read thread) I/O Thread6State:waiting forcompleted AIO requests (write thread) I/O Thread7State:waiting forcompleted AIO requests (write thread) I/O Thread8State:waiting forcompleted AIO requests (write thread) I/O Thread9State:waiting forCompleted AIO requests (write thread)

3. Purge Thread

After a transaction commits, the Undolog used may no longer be needed, so purgethread is required to reclaim the undo pages that have been used and allocated. Before the InnoDB1.1 version, it was done in the master thread, and after InnoDB1.1, a separate thread. In the configuration file, enable the standalone purge thread to be configured as:

    [mysqld]    innnodb_purge_threads=1

After 1.2 version support multiple purge Thread, add book undo page recycling, can be used later statement query:

    ' innodb_purge_threads ' \g;     1. Row ***************************    variable_name:innodb_purge_threads    1    1inset (0.00  sec)    ERROR:    No query specified

4. Page Cleaner Thread

Page Cleaner thread was added in the InnoDB 1.2x version to add a flush of dirty pages from previous versions to a separate thread to alleviate the work of the original master thread and the blocking of the user query thread.

Memory 1. Memory pool

InnoDB is disk-based, and records are managed by page, in order to solve the gap between cup speed and disk speed.

To memorize pages in the database, first put the pages of the disk into the buffer pool, which is called the page FIX in the buffer pool. The next time you read the same page again, read the buffer pool first, read it directly, and read the disk again.

Modifications to the pages in the database also modify the buffer pool first, and then flush to disk at a certain frequency. the size of the buffer pool directly affects the overall performance of the database

Under InnoDB, its buffer pool configuration can be set by innodb_buffer_pool_size, and the query statement is:

    ' innodb_buffer_pool_size ' \g;     1. Row ***************************    variable_name:innodb_buffer_pool_size      134217728    1inset (0.01 sec)

The data page types of the buffer pool cache are:,,,,, 索引页 数据据 undo页 插入缓冲(insert buffer) 自适应哈希索引(adaptive hash index) InnoDB的锁信息(lock info) , 数据字典信息(data dictionary) etc.

Starting with the InnoDB 1.0.x release, multiple buffer pool instances are allowed, the benefit of which is to reduce the resource competition within the database, increase the concurrency of the database, can be innodb_buffer_pool_instances configured by default to 1, the query statement is:

    ' innodb_buffer_pool_instances ' \g;     1. Row ***************************    variable_name:innodb_buffer_pool_instances    1     1inset (0.00 sec)

Viewing the usage status of a buffer pool can be queried using the following syntax:

    mysql> use information_schema;    Database changed    mysqlselect from innodb_buffer_pool_stats\g;     1. Row ***************************    0     8192      7606       586    1 inch Set (0.00 sec)

2. LRU list, free list, and Fush list

The main design of memory pool management, algorithm theory is too strong, see headache

Typically, the buffer pool in the database is managed through the LRU (lastest recent used, least recently used) algorithm.

In InnoDB, the size of the buffer pool page defaults to 16k

LRU list: The most frequently used page is on the first end of the LRU listing, with the least used at the tail end, first releasing the tail end of the page

InnoDB, the LRU algorithm is optimized, and the midpoint position is added to the list, that is, the newly added page is not placed directly in the header, but placed in the LRU list midpoint position, the algorithm is called midpoint insertion strategy, By default the location is 5/8 of the length of the LRU list, which can be innodb_old_blocks_pct controlled by (who fucking bird you)

    ' innodb_old_blocks_pct ' \g;     1. Row ***************************    variable_name:innodb_old_blocks_pct    Notoginseng    1inset (0.00 sec)

In InnoDB, the list after midpoint is called the old list , before the new list

The optimization also adds another parameter, innodb_old_blocks_time, to indicate how long it will take for the page to be read to the mid position before it is added to the new list of the LRU list. Therefore, you can modify this value to make the number of hotspots in the LRU list not be brushed.

    ' Innodb_old_blocks_time ' \g;     1. Row ***************************    variable_name:innodb_old_blocks_time    0    1inset (0.00 sec)

Hard enough, default is 0

When a page is added to the new section from the old part of the LRU list, this operation is called pagemake, and the operation that is not moved from old to new because of the innodb_old_blocks_time setting is called the page not makes young, which can be viewed in the following ways:

Mysql>show engine InnoDB status\g; ***************************1. Row ***************************type:innodb name:status:=====================================141024 7: -: -INNODB MONITOR OUTPUT=====================================    ....    ----------------------BUFFER POOL and MEMORY----------------------Total Memory Allocated137363456;inchAdditional Pool Allocated0Dictionary Memory Allocated219529Buffer Pool Size8192Free buffers5528Database pages2661Old Database pages1002Modified db pages374Pending reads0Pending Writes:lru0, flush List0, single page0Pages made young0, not young0    0.00YOUNGS/S,0.00non-youngs/s Pages Read2655, created6, written A    40.57READS/S,0.12CREATES/S,0.80writes/s Buffer Pool hit rate986/ +, young-making rate0/ +Not0/ +Pages Read Ahead0.00/s, evicted without access0.00/s, Random read ahead0.00/s LRU len:2661, Unzip_lru Len:0I/O sum[0]:cur[ +], unzip sum[0]:cur[0]

You can see that buffer pool size has 8192, which is about 8198*16=127m, (really few)
Free buffers represents the number of pages in the current free list ,
Database pages indicates the number of LRU list pages
YOUNGS/S indicates the number of seconds page make young
NON_YONGS/S: The number of page not make young per second

Important: Buffer pool hitrage, which is typically not less than 95%, if less than 95%, to see if the LRU list is contaminated due to a full table scan

The usage of the buffer pool can be seen in the following syntax:

    Select  from information_schema.innodb_buffer_pool_stats\g;     1. Row ***************************    0     998      65800        1inset (0.00 sec)

You can see the dark body of the LRU table using the following statement:

    Select  from where space=1;     Set (0.16 sec)

Nothing ....

InnoDB support compression function, the original 16K page compression of 1K

When a page in the LRU list is modified, called a dirty page (Dirty page), the data on the buffered page is inconsistent, and the database refreshes the dirty page back to disk through the Checkpioint mechanism, that is, the page in the Flush list is a dirty page list.

In the show engine innodb status
Modified db pages is the number of dirty pages

3. Redo the log buffer

InnoDB Memory Area In addition to the buffer pool, there are redo log buffers (Redolog buffer). InnoDB first puts the redo log into this buffer, and then a certain frequency is flushed to the redo log file. Redo buffer does not need too much, because every second will be flushed to the log file, the default is 8M, can be controlled by Innodb_log_buffer_size

There are three ways to flush the log buffer to the log file:
A. Master thread flushes the redo log buffer to the redo log file every second
B. When each transaction commits
C. When the redo log buffer pool has less than 1/2 remaining space

4. Additional Memory pools

In InnoDB, the management of memory is done in a way called Memory heap (heap), where the memory of some data structures itself needs to be requested from the pool of additional memory, and when the area is insufficient, it is requested from the buffer pool.
For example: Multiple buffer pools are allocated, but the framebuffer ( frame buffer) of each buffer pool also has a corresponding buffer control object (buffer Contrl block), which records some information such as LRU、锁、等待

CheckPoint

The above mentioned need to flush the dirty page to disk, if every dirty page is refreshed, that overhead is too big, but if dirty does not refresh, suddenly the power outage, the data can not be restored. To avoid this situation, the transactional database uses the Write Ahead log Policy , which is to redo the log and then modify the page when the transaction commits. When the crash occurs, the data recovery can be done by redo the log.

Checkpoint is designed to address the following issues:
A. Shortening the recovery time of a database
B. Flushing dirty pages back to disk when buffer pool is insufficient
C. Refresh dirty pages When redo logs are not available

Checkpoint is divided into two types:
A. Sharp Checkpoint
B. Fuzzy Checkpoint

Sharp Checkpoint all dirty pages are brushed back to disk when the database shuts down
Running in the database is a fuzzy Checkpoint that refreshes only a portion of the dirty pages, not all dirty pages.

Fuzzy checkpoint is divided into the following situations:
A. Master Thread Checkpoint
B. Flush_lru_list checkpoint
C. Async/sync Flush Checkpoint
D. Dirty Page too much Checkpoint

Master Thread Checkpoint Flushes a certain percentage of the disk back from the buffer pool at almost the same rate per second

Flush_lru_list Checkpoint is the act of Flushing dirty pages when removing a page from the end of the LRU list
Ayync/sync Flush Checkpoiint is forced to refresh when redo logs are not available

Dirty page Too mush is a forced flush in the case of too many dirty pages. Innodb_max_dirty_pages_pct can be used to control

Mysql Notes (i)