innodb是事物安全的MySQL存儲(chǔ)引擎 是oltp應(yīng)用中核心表的首選存儲(chǔ)引擎

MySQL第一個(gè)支持事物的存儲(chǔ)引擎是BDB

MySQL第一個(gè)完整支持ACID事物是innodb

innodb的特點(diǎn) 行鎖設(shè)計(jì) 支持MVCC 支持外鍵 提供一致性非鎖定讀 同時(shí)被設(shè)計(jì)用來最有效的利用以及使用內(nèi)存和cpu

innodb 體系架構(gòu)

innodb的存儲(chǔ)引擎 有多個(gè)內(nèi)存塊 可以認(rèn)為這些內(nèi)存組成一個(gè)大的內(nèi)存池

1 維護(hù)所有進(jìn)程/線程需要訪問的多個(gè)內(nèi)部數(shù)據(jù)結(jié)構(gòu)

2 緩存磁盤上的數(shù)據(jù) 方便快速地讀取 同時(shí)在對(duì)磁盤文件的數(shù)據(jù)修改之前在這里緩存

3 重做日志(redo log)緩沖

后臺(tái)線程的主要作用是負(fù)責(zé)刷新池中的內(nèi)存池中的數(shù)據(jù) 保證緩沖池中的內(nèi)存是最近的數(shù)據(jù) 將已經(jīng)修改的數(shù)據(jù)文件刷新到磁盤文件 同時(shí)保證在數(shù)據(jù)庫發(fā)生異常的情況下 innodb能恢復(fù)到正常運(yùn)行狀態(tài)

后臺(tái)線程

innodb存儲(chǔ)引擎是多線程的模型 因此其中有多個(gè)不同的后臺(tái)線程 負(fù)責(zé)處理不同的任務(wù)

1 Master Thread

MT 是一個(gè)非常核心的后臺(tái)線程 住一套負(fù)責(zé)將緩沖池中的數(shù)據(jù)一部刷新到磁盤 保證數(shù)據(jù)的一致性 包括臟頁的刷新 合并插入緩沖(insert buffer) undo頁的回收

2 io thread

在innodb存儲(chǔ)引擎中大量使用了AIO(Async IO)來處理些IO請(qǐng)求 這樣可以極大提高數(shù)據(jù)庫的性能 而IO Thread的工作主要負(fù)責(zé)這些io請(qǐng)求的回調(diào)(call back)處理

在innodb 1.0版本之前有4個(gè)io thread 分別是 write read insert buffer和log io thread 但是在Linux平臺(tái)下 io thread的數(shù)量不能進(jìn)行調(diào)整 但是在windows平臺(tái)下可以通過參數(shù)innodb_file_io_threads來增大io thread

從innodb 1.0.x版本開始 read thread和 write thread分別增大到了4個(gè)并且不在使用innodb_file_file_io_threads參數(shù) 而是分別使用 innodb_read_io_threads和innodb_write_io_threads

可以通過命令show engine innodb status來觀察innodb中的IO Thread

show engine innodb status\G;

3 Purge Thread

事物被提交后 其所使用的undolog可能不在需要 因此需要PurgeThread來回收已經(jīng)使用并分配的undo頁

在innodb 1.1版本之前 purge操作僅在innodb存儲(chǔ)引擎的master thread 中完成

而從innodb 1.1版本開始 purge操作可以獨(dú)立到單獨(dú)的線程中進(jìn)行 以此來減輕master thread 的工作 從而提高cpu的使用率以及提升存儲(chǔ)引擎的性能

用戶可以再M(fèi)ySQL數(shù)據(jù)庫的配置文件中添加如下命令來啟用獨(dú)立的Purge Thread

[mysql]

innodb_purge_threads=1

在innodb 1.1 版本中 即使將innodb_purge_threads設(shè)為大于1 innodb存儲(chǔ)引擎啟動(dòng)時(shí)也將其設(shè)為1 并在錯(cuò)誤文件中出現(xiàn)如下類似的提示

在innodb 1.2版本開始 innodb支持多個(gè)purge thread 這樣做的目的是為了進(jìn)一步加快undo頁的回收這樣也能更進(jìn)一步利用磁盤的隨機(jī)讀取性能 用戶可以設(shè)置4個(gè)purge thread

4 page cleaner thread

innodb 1.2.x 引入 page cleaner thread

作用:將之前版本中臧曄的刷新操作都放入到單獨(dú)的線程中完成 減輕原master thread的工作對(duì)于用戶查詢線程的阻塞

內(nèi)存

1 緩沖池

innodb存儲(chǔ)引擎給予磁盤存儲(chǔ)的 并將其中的激勵(lì)按照頁的方式進(jìn)行管理 一次可將其視為給予磁盤的數(shù)據(jù)系統(tǒng)(disk-base database) 由于cpu跟磁盤速度之間的鴻溝 給予磁盤的數(shù)據(jù)庫系統(tǒng)通常使用緩沖池技術(shù)來提高數(shù)據(jù)庫的整體性能

緩沖池就是一塊內(nèi)存區(qū)域 通過內(nèi)存的速度來彌補(bǔ)磁盤的速度較慢對(duì)數(shù)據(jù)庫性能的影響 在數(shù)據(jù)庫中進(jìn)行讀取頁的操作 首先將從磁盤讀到頁存放在緩沖池中 這個(gè)過程稱為將頁FIX 在緩沖池中 下一次在讀相同的頁時(shí) 首先判斷頁是否在緩沖池中 若在緩沖池中 該頁在緩沖池中被命中 直接讀取該頁 否則讀取 磁盤上的頁

對(duì)于數(shù)據(jù)庫中頁的修改操作 則首先修改在緩沖池中的頁 然后再以一定的頻率刷新到磁盤上 這里需要注意的是 頁從緩沖池刷新回磁盤的操作并不是在每次頁發(fā)生更新時(shí)觸發(fā) 而是通過一種稱為checkpoint的機(jī)制刷新回磁盤 同樣 這也是為了提高數(shù)據(jù)庫的整體性能

緩沖池的大小直接影響著數(shù)據(jù)庫的整體性能

緩沖池緩存的數(shù)據(jù)頁的類型有:索引頁 數(shù)據(jù)也 undo頁 插入緩沖(insert buffer) 自適應(yīng)哈希索引(adaptive hash index) innodb存儲(chǔ)鎖信息(lock info) 數(shù)據(jù)字典信息(data dictionary)等 緩沖池不只是緩存索引頁和數(shù)據(jù)頁 他們只是占緩沖池很大的一部分而已

innodb 1.0.x以后 允許多個(gè)緩沖池實(shí)例 每頁根據(jù)哈希值平均分配到不同緩沖池里 這樣做的好處是減少數(shù)據(jù)庫內(nèi)部的資源競(jìng)爭(zhēng) 增加數(shù)據(jù)庫的并發(fā)處理能力

show variables likes 'innodb_buffer_pool_instances'\G;

將這個(gè)參數(shù)改為大于1 的值 就可以得到多個(gè)緩沖池實(shí)例 再通過命令show engine innodb status\G;觀察修改后系統(tǒng)的緩沖池狀態(tài)

MySQL 5.6 可以通過information_schema架構(gòu)下的表 innodb_buffer_pool_status來觀察緩沖的狀態(tài)

2 LRU List Free List Flush List

數(shù)據(jù)庫中的緩沖池是通過LRU(latest recent used 最近最少使用)算法來進(jìn)行管理的 最頻繁使用的頁是在LRU列表的前端 而最少使用的頁在LRU列表的尾端 即使用頻發(fā)的頁放在LRU列表的前端 而最少使用的頁在LRU列表的尾端 當(dāng)緩沖池不能存放新讀取到的頁時(shí) 將首先釋放LRU列表中尾端的頁

在innodb存儲(chǔ)引擎中 緩沖池中頁的大小默認(rèn)為16KB 同樣使用LRU算法對(duì)緩沖池進(jìn)行管理 稍有不同的是innodb存儲(chǔ)引起對(duì)傳統(tǒng)LRU算法做了一些優(yōu)化在innodb的存儲(chǔ)引擎中 LRU列表中還加入了midpoint位置 即新讀取的頁 并不會(huì)直接放到LRU連的首部 而是放到midpoint位置 這個(gè)算法在innodb存儲(chǔ)引擎下稱為 midpoint insertion strategy 默認(rèn)該位置在LRU列表長(zhǎng)度的5/8處

參數(shù):innodb_old_blocks_pct控制

show variables like 'innodb_old_blocks_pct'\G;

采用midpoint的原因

某些sql操作可能會(huì)使緩沖池的頁被刷新出來 從而影響緩沖池的效率 常見的這類操作為索引或數(shù)據(jù)的掃描操作 這類操作需要訪問表中的許多頁甚至全部 而這些頁通常來說又僅在這次查詢操作中需要 并不是活躍的熱點(diǎn)數(shù)據(jù) 如果頁被放到LRU首部 那么非常可能將所需要的熱點(diǎn)數(shù)據(jù)頁從LRU列表一處 而在下一次需要讀取該頁時(shí) innodb存儲(chǔ)引擎需要再次訪問磁盤

innodb_old_blocks_time

用于表示頁讀取到mid未支護(hù)需要等待多久才會(huì)被加入到LRU列表的熱端 因此當(dāng)需要執(zhí)行上述所說的sql操作時(shí) 可以通過下面方法使LRU列表中熱點(diǎn)數(shù)據(jù)不被刷出

set global innodb_old_blocks_time=1000;

如果用戶預(yù)估自己活躍的熱點(diǎn)數(shù)據(jù)不止百分之63 那么執(zhí)行SQL語句前 可以修改innodb_old_blocks_pct 減少熱點(diǎn)頁被刷出來的概率

LRU列表管理已經(jīng)讀取的頁 但數(shù)據(jù)庫剛啟動(dòng)的時(shí)候 LRU列表是空的 即沒有任何的頁 這時(shí)頁都存放在Free列表中 當(dāng)需要從緩沖池中分頁時(shí) 首先從Free列表中查找是否有可用的空閑頁 若有則將該頁從Free列表中刪除 放入到LRU列表中 否則 根據(jù)LRU算法 淘汰LRU列表末尾的頁將該內(nèi)存空間分配給新的頁 當(dāng)頁從LRU列表的old部分 加入到new部分時(shí) 稱此時(shí)發(fā)生的操作為page made young 而因?yàn)閕nnodb_old_blocks_time的設(shè)置而導(dǎo)致頁沒有從old部分移動(dòng)到new部分的操作稱為page noe made young可以通過命令 show engine innodb status來觀察LRU列表及Free列表的使用情況和運(yùn)行狀態(tài) 這個(gè)狀態(tài)顯示的不是當(dāng)前狀態(tài) 而是過去某個(gè)時(shí)間范圍內(nèi) innodb存儲(chǔ)引擎的狀態(tài) per second averages calculated from the last 24 seconds 表示過去的24小時(shí)

page made young 顯示了LRU列表中頁移動(dòng)到前端的次數(shù) 因?yàn)樵摲?wù)器在運(yùn)行階段沒有改變innodb_old_blocks_time的值 因此not young為0 youngs/s non-youngs表示美妙這兩類操作的次數(shù)

buffer pool hit rate 表示緩沖池的命中率 值越接近1或等于1 說明緩沖池運(yùn)行狀態(tài)非常好 一般不應(yīng)該小于百分之九五 若發(fā)生buffer pool hit rate的值小于百分之九五的情況 需要檢查是否由于全表掃描引起的LRU列表被污染的問題

innodb_buffer_pool_stats:觀察緩沖池的運(yùn)行狀態(tài) information_schema中的表

innodb_buffer_page_lru:觀察lru列表中每個(gè)頁的具體信息 information_schema中的表

innodb1.0.x 開始支持壓縮頁的功能 可以講16kb的頁壓縮為1kb 2kb 4kb 和8kb 但是由于壓縮頁的大小發(fā)生變化 lru列表也有了些許的改變 對(duì)于非16kb的頁通過unzip_LRU列表進(jìn)行管理 通過show engine innodbdb status可以觀察

這里 lru len 包含unzip_lru len 列

對(duì)于不同壓縮大小的頁管理方式:在unzip_LRU列表中對(duì)不同壓縮也大小的頁進(jìn)行分別管理 其次通過伙伴算法進(jìn)行內(nèi)存的分配 加入對(duì)需要從緩沖池中申請(qǐng)頁為4kb的大小 過程如下

1 檢查4kb的unzip_LRU列表 檢查是否有可用的空閑頁;

2 若有 則直接使用

3 否則 檢查8kb的unzip_LRU列表;

4 若能的到空閑頁 將頁分成2 個(gè)4kb頁 存放到4kb的unzip_LRU列表

5 若不能夠得到空閑頁 從LRU列表中申請(qǐng)一個(gè)16kb的頁 將頁分為1個(gè)8kb的頁 2個(gè)4kb的頁分別存放到對(duì)應(yīng)的unzip_LRU列表中

同樣可以通過information_schema架構(gòu)下的表innodb_buffer_page_lru來觀察unzip_LRU列表中的頁

LRU列表中的頁被修改后 稱該頁為臟頁(dirty page) 即緩沖池中的頁和磁盤上的頁的數(shù)據(jù)產(chǎn)生了不一致 這時(shí)數(shù)據(jù)庫會(huì)通過checkpoint機(jī)制將臟頁刷新回磁盤 而flush列表中的頁即為臟頁列表 臟頁寄存在與LRU列表中 也存在于flush列表中 LRU列表用來管理緩沖池頁的可用性 Flush用來管理將頁刷新回磁盤二者互不影響

3 重做日志緩存

innodb存儲(chǔ)引擎首先將臭作日志信息先放入到這個(gè)緩沖區(qū) 然后按一定頻率將其刷新到重做日志文件

參數(shù):innodb_log_buffer_size 默認(rèn)8MB

將重做日志緩沖的內(nèi)容刷新到外部磁盤的重做日志文件的情況:

1 master thread 每一秒將重做日志緩沖到重做日志文件

2 每個(gè)事物提交時(shí)會(huì)將重做日志緩沖刷新到重做日志文件

3 當(dāng)重做日志緩沖池剩余空間小于二分之一時(shí) 重做日志緩沖刷新到重做日志文件

4 額外的內(nèi)存池

在innodb存儲(chǔ)引擎 對(duì)內(nèi)存管理是通過一種稱為內(nèi)存堆(heap)的方式進(jìn)行的

4 checkpoint技術(shù)

write ahead log 日志先寫策略 避免數(shù)據(jù)丟失通過重做日志來完成數(shù)據(jù)的恢復(fù)

宕機(jī)通過重做日志恢復(fù)的條件

1 緩沖池可以緩存數(shù)據(jù)庫中所有的數(shù)據(jù)

隨著數(shù)據(jù)庫的日積月累的增大 導(dǎo)致數(shù)據(jù)增到 內(nèi)存不足以緩存所有數(shù)據(jù) 所以對(duì)生產(chǎn)環(huán)境應(yīng)用中的數(shù)據(jù)庫是很難保證的

2 重做日志可以無線增大

重做日志無線增大 會(huì)對(duì)成本要求太高 頁不便于運(yùn)維 而且 DBA 或SA 不知道什么時(shí)候重做日志是否已經(jīng)接近于磁盤可使用的空間的閥值 而且讓存儲(chǔ)設(shè)備支持顆動(dòng)態(tài)擴(kuò)展頁是需要一定的技巧和設(shè)備支持

3 宕機(jī)后的恢復(fù)時(shí)間 時(shí)間越久 恢復(fù)的代價(jià)就會(huì)越大

checkpoint的優(yōu)點(diǎn)

1 縮短數(shù)據(jù)庫的恢復(fù)時(shí)間

2 緩沖池不夠用時(shí) 將臟頁刷新到磁盤

3 重做日志不可用 刷新臟頁

這樣 數(shù)據(jù)庫宕機(jī)的時(shí)候 就不需要所有的重做日志 因?yàn)閏heckpoint之前的頁都已經(jīng)刷新到磁盤 所以這時(shí)候只需要 checkpoint之后的重做日志進(jìn)行恢復(fù) 這樣就大大縮短了恢復(fù)時(shí)間

當(dāng)緩沖池不夠用的時(shí)候 就會(huì)根據(jù) LRU算法會(huì)溢出最近最少使用的頁 若此頁為臟頁 那么需要強(qiáng)制執(zhí)行checkpoint 江臟頁頁就是頁的新版本刷回磁盤

重做日志的循環(huán)使用

對(duì)于innodb存儲(chǔ)引擎 是通過LSN(log sequence number)來標(biāo)記版本 二LSN是8字節(jié)的數(shù)字 單位是字節(jié) 每個(gè)頁都有l(wèi)sn 重做日志頁有LSN checkpoint頁有LSN 可以根據(jù) show engine innodb status來觀察

checkpoint作用：將緩沖池中的臟頁刷會(huì)磁盤 但是每次刷多少頁到磁盤 從哪取臟頁 什么時(shí)候觸發(fā)checkpoint都不同

checkpoint類型

1 sharp checkpoint

作用：數(shù)據(jù)庫關(guān)閉時(shí)將所有臟頁都刷新到磁盤 這是默認(rèn)工作方式

參數(shù)：innodb_fast_shutdown=1

2 fuzzy checkpoint

如果數(shù)據(jù)庫正常運(yùn)行的時(shí)候使用sharp checkpoint 那么數(shù)據(jù)庫的可能性就會(huì)收到很大影響 所以在innodb存儲(chǔ)引擎內(nèi)部使用 fuzzy checkpoint進(jìn)行頁的刷新 (只刷新一部分臟頁 而不是刷新所有的臟頁回磁盤)

發(fā)生 fuzzy checkpoint情況

1 master threadcheckpoint

差不多以每秒或者每10秒的速度從緩沖池臟頁列表刷新一定比例的頁回磁盤 (異步 查詢線程不會(huì)阻塞)

2 flush_lru_list checkpoint

innodb 1.1x版本前 需要檢查L(zhǎng)RU列表中是否有足夠的(100)可用空間操作發(fā)生在用戶查詢線程中 會(huì)阻塞查詢 沒有 innodb存儲(chǔ)引擎就會(huì)將lru列表尾端的頁溢出 如果這些頁里面有臟頁 就需要checkpoint

innodb 1.2.x版本開始 這個(gè)檢查被放在了一個(gè)單獨(dú)的page cleaner 線程中進(jìn)行 并且用戶可以通過innod_lruscan depth控制列表中可用頁的數(shù)量 默認(rèn)為1024

3 async/sync flush checkpoint

當(dāng)重做日志不可用的情況下 這時(shí)需要強(qiáng)制將一些頁刷新會(huì)磁盤 而此時(shí)臟頁是從臟頁列表中選取 若將已經(jīng)寫入到重做日志的LSN 記為 redo_lsn 將已經(jīng)刷新會(huì)磁盤最新頁的lsn結(jié)尾checkpoint_lsn

checkpoint_age = redo_lsn - checkpoint_lsn

async_water_mark = 75% total_redo_log_file_size

async/sync flush checkpoint 是為了保證重做日志的循環(huán)使用的可用性

innodb 1.2x之前 async flush checkpoint會(huì)阻塞發(fā)現(xiàn)問題的用戶查詢線程 sync flush checkpoint會(huì)阻塞用戶查詢線程 并等待臟頁的刷新完成

MySQL官方版本 并不能查看刷新也是從flush 列表還是LRU列表中進(jìn)行checkpoint的 也不知道因?yàn)橹刈鋈罩径a(chǎn)生的async/sync flush的次數(shù) 但是 innoSQL版本可以通過 show engine innodb status來觀察

4 dirty page too much checkpoint

臟頁太多導(dǎo)致innodb存儲(chǔ)引擎強(qiáng)制進(jìn)行checkpoint 其目的總的來說還是為了保證緩沖池中有足夠多的頁 主要是為了保證緩沖池中有足夠可用的頁

參數(shù)innodb_max_dirty_pages_pct

5 master thread 工作方式

innodb 1.0.x 之前的master thread

master thread 具有最高的線程優(yōu)先級(jí)別 其內(nèi)部由多個(gè)循環(huán)(loop)組成 主循環(huán) (loop) 后臺(tái)循環(huán)(backgroup loop) 刷新循環(huán)(flush loop) 暫停循環(huán)(suspend loop) master thread就會(huì)在這些循環(huán)中切換

loop為主循環(huán)

loop循環(huán)通過thread sleep來實(shí)現(xiàn) 在負(fù)載很大的情況下可能會(huì)有延遲

每秒一次的操作包括

1 日志緩沖刷新到磁盤 即使這個(gè)事務(wù)還沒有提交(總是)

2 合并插入緩沖(可能)

3 至多刷新100個(gè)innodb的緩沖池中的臟頁到磁盤(可能)

4 如果當(dāng)前沒有用戶活動(dòng) 則切換到 background loop(可能)

再大的事務(wù)提交的時(shí)間很短的原因:innodb存儲(chǔ)引擎仍然每秒會(huì)將重做日志緩沖中的內(nèi)容刷新到重做日志文件

合并插入緩沖 也不是每秒都會(huì)發(fā)生的 innodb存儲(chǔ)引擎會(huì)判斷當(dāng)前一秒內(nèi)發(fā)生的io次數(shù)是否小于5次 如果小于5次 innodb人為當(dāng)前io壓力很小 可以執(zhí)行合并插入緩沖的操作

buf_get_modified_ratio_pct跟百分之90的innodb_max_dirty_pages_pct相比較 超過 才會(huì)人為需要進(jìn)行磁盤同步操作 將100個(gè)臟頁寫入磁盤

每10秒的操作

1 刷新100個(gè)臟頁到磁盤(可能)

2 合并之多5個(gè)插入緩沖(總是)

3 將日志緩沖刷新到磁盤(總是)

4 刪除無用的undo頁(總是)

5 刷新100或者10個(gè)臟頁到磁盤(總是)

過程

1 判斷10秒之內(nèi)的磁盤的IO操作是否小于200

2 小于 innodb認(rèn)為有足夠的IO能力 將100的臟頁刷新到磁盤

3 innodb存儲(chǔ)引擎會(huì)合并插入緩沖 這次合并插入緩沖總會(huì)在這個(gè)剪短進(jìn)行

4 innodb存儲(chǔ)引擎會(huì)再進(jìn)行一次獎(jiǎng)入職緩沖刷新到磁盤的操作 這和沒秒一次發(fā)生的操作一樣

5 innodb進(jìn)一步執(zhí)行full purge 即刪除無用的undo頁 對(duì)表update delete這類操作時(shí)操作時(shí) 原先的行被標(biāo)記為刪除 但是因?yàn)橐恢滦宰x(consistent read)的關(guān)系 需要保留這些行版本的信息 但是在full purge過程中 innodb存儲(chǔ)引擎會(huì)判斷當(dāng)前事物系統(tǒng)已被刪除的行是否可以刪除 比如有時(shí)候還有查詢操作需要讀取之前版本的undo信息 如果可以刪除 innodb會(huì)立即將其刪除

6 buf_get_modified_ratio_pct >70% 則刷新100個(gè)臟頁到磁盤 <70%刷新10%的臟頁到磁盤

background loop 當(dāng)當(dāng)前沒有用戶活躍(數(shù)據(jù)庫空閑)或者數(shù)據(jù)庫關(guān)閉(shutdown)

進(jìn)行的操作

1 刪除無用的undo(總是)

2 合并20個(gè)插入緩沖(總是)

3 跳回到主循環(huán)(總是)

4 不斷刷新100個(gè)頁知道符合條件(可能 跳轉(zhuǎn)到flush loop中完成)

跳轉(zhuǎn)到flush loop 后 如果也沒什么事情做了 innodb存儲(chǔ)引擎會(huì)切換到suspend loop 將master thread掛起 等待事件的發(fā)生 若用戶啟用了(enable)innodb 卻沒有使用任何innodb的表 那么master thread 一直掛起狀態(tài)

innodb 1.2.x之前的master thread

參數(shù) innodb_io_capacity 用來表示磁盤io的吞吐量 默認(rèn)是200 對(duì)于刷新到磁盤頁的數(shù)量 按照innodb_io_capacity的百分比控制

1 在合并插入緩沖時(shí) 合并插入緩沖的數(shù)量為 innodb_io_capacity

2 在從緩沖區(qū)刷新臟頁時(shí) 刷新臟頁的數(shù)量為innodb_io_capacity

用來解決寫入密集的應(yīng)用程序中 產(chǎn)生大于20額插入緩沖的情況 master thread忙不來的問題

參數(shù) innodb_max_dirty_pages_pct