Mysql 筆記(一)

標籤：style   blog   http   color   io   ar   使用   for   strong   

InnoDB儲存引擎

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

簡介

InnoDB是事務安全的MySQL儲存引擎，從MySQL5.5版本開始是預設的表格儲存體引擎，是第一個完整支援ACID事務的MySQL儲存引擎，其特點是行鎖設計、支援MVCC、支援外鍵、提供一致性鎖定讀，同時被設計用來最有效地利用以及使用記憶體和CPU

InnoDB儲存引擎體系架構

後台線程(多個)->InnoDB儲存引擎記憶體池->物理檔案

後台線程1. Master Thread

核心的後台線程，負責將緩衝池中的資料非同步重新整理到磁碟，保證資料的一致性，包括髒頁的重新整理、合并插入緩衝(INSERT BUFFER)、 UNDO頁的回收等

2. IO Thread

在InnoDB中大量使用了AIO（Async IO）來處理IO 請求，IO Thread主要是負責這些IO請求的回調處理。Io Thread共有4類：write、read、insert buffer和log IO thread。在InnoDB 1.0.x 版本開始，read thread和write thread分別增加大4個，用innodb_file_io_threads和innodb_write_io_threads參數來設定，如
可以看到thread 0為insert buffer thread,IO thread 1為log thread.

    mysql> show engine innodb status\G;    *************************** 1. row ***************************    Type: InnoDB    Name:    ....    --------    FILE I/O    --------    I/O thread 0 state: waiting for completed aio requests (insert buffer thread)    I/O thread 1 state: waiting for completed aio requests (log thread)    I/O thread 2 state: waiting for completed aio requests (read thread)    I/O thread 3 state: waiting for completed aio requests (read thread)    I/O thread 4 state: waiting for completed aio requests (read thread)    I/O thread 5 state: waiting for completed aio requests (read thread)    I/O thread 6 state: waiting for completed aio requests (write thread)    I/O thread 7 state: waiting for completed aio requests (write thread)    I/O thread 8 state: waiting for completed aio requests (write thread)    I/O thread 9 state: waiting for completed aio requests (write thread)

 

3. Purge Thread

事務提交後，使用的undolog可能不再需要，因此需要PurgeThread 來回收已經使用並分配的undo頁面。在InnoDB1.1版本前，它是在Master線程中完成的，InnoDB1.1後，獨立一條線程。在設定檔中 啟用獨立Purge Thread線程配置為：

    [mysqld]    innnodb_purge_threads=1

 

在1.2版本後支援多個Purge Thread，加書undo頁的回收，可用以後語句查詢：

    mysql> show variables like ‘innodb_purge_threads‘\G;    *************************** 1. row ***************************    Variable_name: innodb_purge_threads    Value: 1    1 row in set (0.00 sec)    ERROR:    No query specified

 

4. Page Cleaner Thread

Page Cleaner Thread是在InnoDB 1.2x版本加入的，作用是將之前版本中髒頁的重新整理操作都加入到單獨的線程中完成，目的是為了減輕原Master Thread的工作和使用者查詢線程的阻塞。

記憶體1. 記憶體池

InnoDB是基於磁碟儲存的，並將記錄按照頁的方式進行管理，記憶體池就是為瞭解決CUP速度與磁碟速度之間的鴻溝。

在資料庫中進行記取頁的操作，首先將磁碟的頁放到緩衝池，這個過程稱將頁FIX在緩衝池。下一次再讀相同的頁時，首先讀緩衝池，有直接讀，沒有再去讀磁碟

對資料庫中頁的修改也首先修改緩衝池，再以一定的頻率重新整理到磁碟上。緩衝池的大小直接影響資料庫的整體效能

在InnoDB下，其緩衝池配置可通過innodb_buffer_pool_size來設定，查詢語句為：

    mysql> show variables like ‘innodb_buffer_pool_size‘\G;    *************************** 1. row ***************************    Variable_name: innodb_buffer_pool_size    Value: 134217728    1 row in set (0.01 sec)

 

緩衝池緩衝的資料頁類型有：索引頁、資料據、undo頁、插入緩衝(insert buffer)、自適應雜湊索引(adaptive hash index)、InnoDB的鎖資訊(lock info)、資料字典資訊(data dictionary)等

從InnoDB 1.0.x版本開始，允許多個緩衝池執行個體，這樣做的好處是減少資料庫內部的資源競爭，增加資料庫的並發能力，可以通過innodb_buffer_pool_instances來配置，預設為1，查詢語句為：

    mysql> show variables like ‘innodb_buffer_pool_instances‘\G;    *************************** 1. row ***************************    Variable_name: innodb_buffer_pool_instances    Value: 1    1 row in set (0.00 sec)

 

查看緩衝池的使用狀態可以通過以下文法查詢：

    mysql> use information_schema;    Database changed    mysql> select pool_id,pool_size,free_buffers,database_pages from INNODB_BUFFER_POOL_STATS\G;    *************************** 1. row ***************************    pool_id: 0    pool_size: 8192    free_buffers: 7606    database_pages: 586    1 row in set (0.00 sec)

 

2. LRU List、Free List和 Fush List

主要設計對記憶體池的管理，演算法理論太強，看得頭痛

通常來說，資料庫中的緩衝池是通過LRU(Lastest Recent Used,最近最少使用)演算法來管理的。

在InnoDB中，緩衝池頁的大小預設為16k

LRU List:最頻繁使用的頁在LRU列表的首端，最少使用的在尾端，先釋放尾端的頁

InnoDB中，最佳化了LRU演算法，在列表中加入了midpoint位置，就是說，新加入的頁不是直接放在首部，而是放在LRU列表中的 midpoint位置，這個演算法稱為midpoint insertion strategy，預設下該位置 是在LRU列表長度的5/8，可通過innodb_old_blocks_pct控制(誰他媽的鳥你)

    mysql> show variables like ‘innodb_old_blocks_pct‘\G;    *************************** 1. row ***************************    Variable_name: innodb_old_blocks_pct    Value: 37    1 row in set (0.00 sec)

 

在innoDB中，midpoint之後的列表稱為old列表，之前的為new列表

最佳化還加入了另一個參數innodb_old_blocks_time，用來表示頁讀取到mid位置後需要等待多久才會被加入到LRU列表的new列表，因此可以通過修改這個值來盡可以使LRU列表中的熱點數不被刷出。

    mysql> show variables like ‘innodb_old_blocks_time‘\G;    *************************** 1. row ***************************    Variable_name: innodb_old_blocks_time    Value: 0    1 row in set (0.00 sec)

 

夠狠，預設為0

當頁從LRU列表的old部分加入new部分，這個操作稱為page make young,而因innodb_old_blocks_time的設定而導致沒有從old移動new的操作稱為page not make young，可以通過以下方法查看：

    mysql> show engine innodb status\G;    *************************** 1. row ***************************    Type: InnoDB    Name:    Status:    =====================================    141024 7:50:59 INNODB MONITOR OUTPUT    =====================================    ....    ----------------------    BUFFER POOL AND MEMORY    ----------------------    Total memory allocated 137363456; in additional pool allocated 0    Dictionary memory allocated 219529    Buffer pool size 8192    Free buffers 5528    Database pages 2661    Old database pages 1002    Modified db pages 374    Pending reads 0    Pending writes: LRU 0, flush list 0, single page 0    Pages made young 0, not young 0    0.00 youngs/s, 0.00 non-youngs/s    Pages read 2655, created 6, written 41    40.57 reads/s, 0.12 creates/s, 0.80 writes/s    Buffer pool hit rate 986 / 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: 2661, unzip_LRU len: 0    I/O sum[0]:cur[32], unzip sum[0]:cur[0]

 

可以看到buffer pool size有8192,即8198*16=127M左右，（真少）
Free buffers表示當前Free列表中頁的數量，
Database pages表示LRU列表頁的數量
youngs/s表示第秒page make young的次數
non_yongs/s:每秒page not make young的次數

重要：Buffer pool hit rage,表示緩衝池的命中率，這個通常不小於95%，如果小於95%，請觀察是否由於全表掃描引起的LRU列表被汙染

可以通過以下文法看緩衝池的使用方式：

    mysql> select pool_id,hit_rate,pages_made_young,pages_not_made_young from information_schema.innodb_buffer_pool_stats\G;    *************************** 1. row ***************************    pool_id: 0    hit_rate: 998    pages_made_young: 6580    pages_not_made_young: 0    1 row in set (0.00 sec)

 

可以通過以下語句看LRU表的暗體情況：

    mysql> select table_name,space,page_number,page_type from innodb_buffer_page_lru where space=1;    Empty set (0.16 sec)

 

沒東西....

InnoDB支援壓縮功能，原來16K的頁壓縮為1K

LRU列表中的頁被修改後，稱為髒頁(dirty page)，即緩衝頁面上的資料產生了不一致，這時候資料庫會通過CHECKPIOINT機制將髒頁重新整理回磁碟，即Flush列表中的頁為髒頁列表。

在show engine innodb status中
Modified db pages為是髒頁數目

3. 重做日誌緩衝

InnoDB記憶體區除了緩衝池外，還有重做日誌緩衝(redolog buffer)。Innodb首先將重做日誌放到這個緩衝，再一定頻率重新整理到重做記錄檔。重做緩衝不用太多，因為每一秒就會重新整理到記錄檔，預設為8M，可通過innodb_log_buffer_size控制

把日誌緩衝重新整理到記錄檔中的情況有以下三種 ：
a. Master Thread每一秒將重做日誌緩衝重新整理到重做記錄檔中
b. 每個事務提交時
c.當重做日誌緩衝池剩餘空間小於1/2時

4. 額外的記憶體池

在InnoDB中，對記憶體的管理是通過一種 稱為記憶體堆 （heap)的方式進行的，在對一些資料結構本身的記憶體進行分配 時，需要從額外記憶體池中申請，當該區不夠時，會從緩衝池中申請。
例如：分配 了多個緩衝池，但是每個緩衝池的幀緩衝(frame buffer)還有對應的緩衝控制對象(buffer contrl block)，這些對象記錄一些如LRU、鎖、等待等資訊

CheckPoint

上面提到需要把髒頁重新整理到磁碟，若每一次弄髒的頁都重新整理，那開銷太大，但是如果髒了不重新整理，突然停電死機了，那資料就不能恢複了。為了避免這種情況，交易資料庫都採用了Write Ahead Log策略，就是當事務提交時，先做重做日誌，再修改頁。當死機時，可以通過重做日誌來完成資料的恢複。

CheckPoint是為瞭解決以下問題：
a. 縮短資料庫的恢復
b. 緩衝池不夠用時，將髒頁重新整理回磁碟
c. 重做日誌不可用時，重新整理髒頁

CheckPoint分兩種 ：
a. Sharp Checkpoint
b. Fuzzy Checkpoint

Sharp Checkpoint 發生 在資料庫關閉時將所有的髒頁刷回磁碟
在資料庫運行時啟動並執行是Fuzzy Checkpoint,就是只重新整理一部分髒頁，而不是重新整理所有的髒頁

Fuzzy CheckPoint分以下幾種 情況：
a. Master Thread Checkpoint
b. Flush_lru_list checkpoint
c. Async/Sync Flush Checkpoint
d. Dirty Page too much Checkpoint

Master Thread Checkpoint 差不多以每秒的速度從緩衝池重新整理一定比例回磁碟

Flush_lru_list checkpoint是在移除LRU列表的尾端的頁面時，重新整理髒頁的操作
Ayync/Sync Flush Checkpoiint是在重做日誌不可用時，強制重新整理

Dirty page too mush是在髒頁太多的情況下，強制重新整理。可用 innodb_max_dirty_pages_pct來控制

Mysql 筆記(一)