innodb是事物安全的MySQL儲存引擎 是oltp應用中核心表的首選儲存引擎
MySQL第一個支援事物的儲存引擎是BDB
MySQL第一個完整支援ACID事物是innodb
innodb的特點 行鎖設計 支援MVCC 支援外鍵 提供一致性非鎖定讀 同時被設計用來最有效利用以及使用記憶體和cpu
|
版本
|
功能
|
|
老版本innodb
|
支援ACID 行鎖設計 MVCC
|
|
innodb 1.0.x
|
增加了compress和dynamic頁格式 |
|
innodb 1.1.x
|
增加了Linux AIO 多復原段
|
|
innodb 1.2.x
|
增加了全文索引支援 線上索引添加
|
innodb 體系架構
innodb的儲存引擎 有多個記憶體塊 可以認為這些記憶體組成一個大的記憶體池
1 維護所有進程/線程需要訪問的多個內部資料結構
2 緩衝磁碟上的資料 方便快速地讀取 同時在對磁碟檔案的資料修改之前在這裡緩衝
3 重做日誌(redo log)緩衝
ps://yqfile.alicdn.com/ccc45cfe41bcff82cb232618f713c01c9765dd4b.png" />
後台線程的主要作用是負責重新整理池中的記憶體池中的資料 保證緩衝池中的記憶體是最近的資料 將已經修改的資料檔案重新整理到磁碟檔案 同時保證在資料庫發生異常的情況下 innodb能恢複到正常運行狀態
後台線程
innodb儲存引擎是多線程的模型 因此其中有多個不同的後台線程 負責處理不同的任務
1 Master Thread
MT 是一個非常核心的後台線程 住一套負責將緩衝池中的資料一部重新整理到磁碟 保證資料的一致性 包括髒頁的重新整理 合并插入緩衝(insert buffer) undo頁的回收
2 io thread
在innodb儲存引擎中大量使用了AIO(Async IO)來處理些IO請求 這樣可以極大提高資料庫的效能 而IO Thread的工作主要負責這些io請求的回調(call back)處理
在innodb 1.0版本之前有4個io thread 分別是 write read insert buffer和log io thread 但是在Linux平台下 io thread的數量不能進行調整 但是在windows平台下可以通過參數innodb_file_io_threads來增大io thread
從innodb 1.0.x版本開始 read thread和 write thread分別增大到了4個並且不在使用innodb_file_file_io_threads參數 而是分別使用 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來回收已經使用並分配的undo頁
在innodb 1.1版本之前 purge操作僅在innodb儲存引擎的master thread 中完成
而從innodb 1.1版本開始 purge操作可以獨立到單獨的線程中進行 以此來減輕master thread 的工作 從而提高cpu的使用率以及提升儲存引擎的效能
使用者可以再MySQL資料庫的設定檔中添加如下命令來啟用獨立的Purge Thread
[mysql]
innodb_purge_threads=1
在innodb 1.1 版本中 即使將innodb_purge_threads設為大於1 innodb儲存引擎啟動時也將其設為1 並在錯誤檔案中出現如下類似的提示
在innodb 1.2版本開始 innodb支援多個purge thread 這樣做的目的是為了進一步加快undo頁的回收這樣也能更進一步利用磁碟的隨機讀取效能 使用者可以設定4個purge thread
4 page cleaner thread
innodb 1.2.x 引入 page cleaner thread
作用:將之前版本中臧曄的重新整理操作都放入到單獨的線程中完成 減輕原master thread的工作對於使用者查詢線程的阻塞
記憶體
1 緩衝池
innodb儲存引擎給予磁碟儲存的 並將其中的激勵按照頁的方式進行管理 一次可將其視為給予磁碟的資料系統(disk-base database) 由於cpu跟磁碟速度之間的鴻溝 給予磁碟的資料庫系統通常使用緩衝池技術來提高資料庫的整體效能
緩衝池就是一塊記憶體地區 通過記憶體的速度來彌補磁碟的速度較慢對資料庫效能的影響 在資料庫中進行讀取頁的操作 首先將從磁碟讀到頁存放在緩衝池中 這個過程稱為將頁FIX 在緩衝池中 下一次在讀相同的頁時 首先判斷頁是否在緩衝池中 若在緩衝池中 該頁在緩衝池中被命中 直接讀取該頁 否則讀取 磁碟上的頁
對於資料庫中頁的修改操作 則首先修改在緩衝池中的頁 然後再以一定的頻率重新整理到磁碟上 這裡需要注意的是 頁從緩衝池重新整理回磁碟的操作並不是在每次頁發生更新時觸發 而是通過一種稱為checkpoint的機制重新整理回磁碟 同樣 這也是為了提高資料庫的整體效能
緩衝池的大小直接影響著資料庫的整體效能
緩衝池緩衝的資料頁的類型有:索引頁 資料也 undo頁 插入緩衝(insert buffer) 自適應雜湊索引(adaptive hash index) innodb儲存鎖資訊(lock info) 資料字典資訊(data dictionary)等 緩衝池不只是緩衝索引頁和資料頁 他們只是占緩衝池很大的一部分而已
innodb 1.0.x以後 允許多個緩衝池執行個體 每頁根據雜湊值平均分配到不同緩衝池裡 這樣做的好處是減少資料庫內部的資源競爭 增加資料庫的並發處理能力
show variables likes 'innodb_buffer_pool_instances'\G;
將這個參數改為大於1 的值 就可以得到多個緩衝池執行個體 再通過命令show engine innodb status\G;觀察修改後系統的緩衝池狀態
MySQL 5.6 可以通過information_schema架構下的表 innodb_buffer_pool_status來觀察緩衝的狀態
2 LRU List Free List Flush List
資料庫中的緩衝池是通過LRU(latest recent used 最近最少使用)演算法來進行管理的 最頻繁使用的頁是在LRU列表的前端 而最少使用的頁在LRU列表的尾端 即使用頻發的頁放在LRU列表的前端 而最少使用的頁在LRU列表的尾端 當緩衝池不能存放新讀取到的頁時 將首先釋放LRU列表中尾端的頁
在innodb儲存引擎中 緩衝池中頁的大小預設為16KB 同樣使用LRU演算法對緩衝池進行管理 稍有不同的是innodb儲存引起對傳統LRU演算法做了一些最佳化在innodb的儲存引擎中 LRU列表中還加入了midpoint位置 即新讀取的頁 並不會直接放到LRU連的首部 而是放到midpoint位置 這個演算法在innodb儲存引擎下稱為 midpoint insertion strategy 預設該位置在LRU列表長度的5/8處
參數:innodb_old_blocks_pct控制
show variables like 'innodb_old_blocks_pct'\G;
採用midpoint的原因
某些sql操作可能會使緩衝池的頁被重新整理出來 從而影響緩衝池的效率 常見的這類操作為索引或資料的掃描操作 這類操作需要訪問表中的許多頁甚至全部 而這些頁通常來說又僅在這次查詢操作中需要 並不是活躍的熱點資料 如果頁被放到LRU首部 那麼非常可能將所需要的熱點資料頁從LRU列表一處 而在下一次需要讀取該頁時 innodb儲存引擎需要再次訪問磁碟
innodb_old_blocks_time
用於表示頁讀取到mid未支護需要等待多久才會被加入到LRU列表的熱端 因此當需要執行上述所說的sql操作時 可以通過下面方法使LRU列表中熱點資料不被刷出
set global innodb_old_blocks_time=1000;
如果使用者預估自己活躍的熱點資料不止百分之63 那麼執行SQL語句前 可以修改innodb_old_blocks_pct 減少熱點頁被刷出來的機率
LRU列表管理已經讀取的頁 但資料庫剛啟動的時候 LRU列表是空的 即沒有任何的頁 這時頁都存放在Free列表中 當需要從緩衝池中分頁時 首先從Free列表中尋找是否有可用的空閑頁 若有則將該頁從Free列表中刪除 放入到LRU列表中 否則 根據LRU演算法 淘汰LRU列表末尾的頁將該記憶體空間分配給新的頁 當頁從LRU列表的old部分 加入到new部分時 稱此時發生的操作為page made young 而因為innodb_old_blocks_time的設定而導致頁沒有從old部分移動到new部分的操作稱為page noe made young可以通過命令 show engine innodb status來觀察LRU列表及Free列表的使用方式和運行狀態 這個狀態顯示的不是目前狀態 而是過去某個時間範圍內 innodb儲存引擎的狀態 per second averages calculated from the last 24 seconds 表示過去的24小時
page made young 顯示了LRU列表中頁移動到前端的次數 因為該伺服器在運行階段沒有改變innodb_old_blocks_time的值 因此not young為0 youngs/s non-youngs表示美妙這兩類操作的次數
buffer pool hit rate 表示緩衝池的命中率 值越接近1或等於1 說明緩衝池運行狀態非常好 一般不應該小於百分之九五 若發生buffer pool hit rate的值小於百分之九五的情況 需要檢查是否由於全表掃描引起的LRU列表被汙染的問題
innodb_buffer_pool_stats:觀察緩衝池的運行狀態 information_schema中的表
innodb_buffer_page_lru:觀察lru列表中每個頁的具體資訊 information_schema中的表
innodb1.0.x 開始支援壓縮頁的功能 可以講16kb的頁壓縮為1kb 2kb 4kb 和8kb 但是由於壓縮頁的大小發生變化 lru列表也有了些許的改變 對於非16kb的頁通過unzip_LRU列表進行管理 通過show engine innodbdb status可以觀察
這裡 lru len 包含unzip_lru len 列
對於不同壓縮大小的頁管理方式:在unzip_LRU列表中對不同壓縮也大小的頁進行分別管理 其次通過夥伴演算法進行記憶體的分配 加入對需要從緩衝池中申請頁為4kb的大小 過程如下
1 檢查4kb的unzip_LRU列表 檢查是否有可用的空閑頁;
2 若有 則直接使用
3 否則 檢查8kb的unzip_LRU列表;
4 若能的到空閑頁 將頁分成2 個4kb頁 存放到4kb的unzip_LRU列表
5 若不能夠得到空閑頁 從LRU列表中申請一個16kb的頁 將頁分為1個8kb的頁 2個4kb的頁分別存放到對應的unzip_LRU列表中
同樣可以通過information_schema架構下的表innodb_buffer_page_lru來觀察unzip_LRU列表中的頁
LRU列表中的頁被修改後 稱該頁為髒頁(dirty page) 即緩衝池中的頁和磁碟上的頁的資料產生了不一致 這時資料庫會通過checkpoint機制將髒頁重新整理回磁碟 而flush列表中的頁即為髒頁列表 髒頁寄存在與LRU列表中 也存在於flush列表中 LRU列表用來管理緩衝池頁的可用性 Flush用來管理將頁重新整理回磁碟二者互不影響
3 重做日誌緩衝
innodb儲存引擎首先將臭作日誌資訊先放入到這個緩衝區 然後按一定頻率將其重新整理到重做記錄檔
參數:innodb_log_buffer_size 預設8MB
將重做日誌緩衝的內容重新整理到外部磁碟的重做記錄檔的情況:
1 master thread 每一秒將重做日誌緩衝到重做記錄檔
2 每個事物提交時會將重做日誌緩衝重新整理到重做記錄檔
3 當重做日誌緩衝池剩餘空間小於二分之一時 重做日誌緩衝重新整理到重做記錄檔
4 額外的記憶體池
在innodb儲存引擎 對記憶體管理是通過一種稱為記憶體堆(heap)的方式進行的
4 checkpoint技術
write ahead log 日誌先寫策略 避免資料丟失通過重做日誌來完成資料的恢複
宕機通過重做日誌恢複的條件
1 緩衝池可以快取資料庫中所有的資料
隨著資料庫的日積月累的增大 導致資料增到 記憶體不足以緩衝所有資料 所以對生產環境應用中的資料庫是很難保證的
2 重做日誌可以無線增大
重做日誌無線增大 會對成本要求太高 頁不便於營運 而且 DBA 或SA 不知道什麼時候重做日誌是否已經接近於磁碟可使用的空間的閥值 而且讓存放裝置支援顆動態擴充頁是需要一定的技巧和裝置支援
3 宕機後的恢復 時間越久 恢複的代價就會越大
checkpoint的優點
1 縮短資料庫的恢復
2 緩衝池不夠用時 將髒頁重新整理到磁碟
3 重做日誌不可用 重新整理髒頁
這樣 資料庫宕機的時候 就不需要所有的重做日誌 因為checkpoint之前的頁都已經重新整理到磁碟 所以這時候只需要 checkpoint之後的重做日誌進行恢複 這樣就大大縮短了恢復
當緩衝池不夠用的時候 就會根據 LRU演算法會溢出最近最少使用的頁 若此頁為髒頁 那麼需要強制執行checkpoint 江髒頁頁就是頁的新版本刷回磁碟
重做日誌的迴圈使用
對於innodb儲存引擎 是通過LSN（log sequence number）來標記版本 二LSN是8位元組的數字 單位是位元組 每個頁都有lsn 重做日誌頁有LSN checkpoint頁有LSN 可以根據 show engine innodb status來觀察
checkpoint作用：將緩衝池中的髒頁刷會磁碟 但是每次刷多少頁到磁碟 從哪取髒頁 什麼時候觸發checkpoint都不同
checkpoint類型
1 sharp checkpoint
作用：資料庫關閉時將所有髒頁都重新整理到磁碟 這是預設工作方式
參數：innodb_fast_shutdown=1
2 fuzzy checkpoint
如果資料庫正常啟動並執行時候使用sharp checkpoint 那麼資料庫的可能性就會收到很大影響 所以在innodb儲存引擎內部使用 fuzzy checkpoint進行頁的重新整理 （只重新整理一部分髒頁 而不是重新整理所有的髒頁回磁碟）
發生 fuzzy checkpoint情況
1 master threadcheckpoint
差不多以每秒或者每10秒的速度從緩衝池髒頁列表重新整理一定比例的頁回磁碟 （非同步 查詢線程不會阻塞）
2 flush_lru_list checkpoint
innodb 1.1x版本前 需要檢查LRU列表中是否有足夠的（100）可用空間操作發生在使用者查詢線程中 會阻塞查詢 沒有 innodb儲存引擎就會將lru列表尾端的頁溢出 如果這些頁裡面有髒頁 就需要checkpoint
innodb 1.2.x版本開始 這個檢查被放在了一個單獨的page cleaner 線程中進行 並且使用者可以通過innod_lruscan depth控制列表中可用頁的數量 預設為1024
3 async/sync flush checkpoint
當重做日誌停用情況下 這時需要強制將一些頁重新整理會磁碟 而此時髒頁是從髒頁列表中選取 若將已經寫入到重做日誌的LSN 記為 redo_lsn 將已經重新整理會磁碟最新頁的lsn結尾checkpoint_lsn
checkpoint_age = redo_lsn - checkpoint_lsn
async_water_mark = 75% total_redo_log_file_size
async/sync flush checkpoint 是為了保證重做日誌的迴圈使用的可用性
innodb 1.2x之前 async flush checkpoint會阻塞發現問題的使用者查詢線程 sync flush checkpoint會阻塞使用者查詢線程 並等待髒頁的重新整理完成
MySQL官方版本 並不能查看重新整理也是從flush 列表還是LRU列表中進行checkpoint的 也不知道因為重做日誌而產生的async/sync flush的次數 但是 innoSQL版本可以通過 show engine innodb status來觀察
4 dirty page too much checkpoint
髒頁太多導致innodb儲存引擎強制進行checkpoint 其目的總的來說還是為了保證緩衝池中有足夠多的頁 主要是為了保證緩衝池中有足夠可用的頁
參數innodb_max_dirty_pages_pct
5 master thread 工作方式
innodb 1.0.x 之前的master thread
master thread 具有最高的線程優先順序別 其內部由多個迴圈(loop)組成 主迴圈 (loop) 後台迴圈(backgroup loop) 重新整理迴圈(flush loop) 暫停迴圈(suspend loop) master thread就會在這些迴圈中切換
loop為主迴圈
loop迴圈通過thread sleep來實現 在負載很大的情況下可能會有延遲
每秒一次的操作包括
1 日誌緩衝重新整理到磁碟 即使這個事務還沒有提交(總是)
2 合并插入緩衝(可能)
3 至多重新整理100個innodb的緩衝池中的髒頁到磁碟(可能)
4 如果當前沒有使用者活動 則切換到 background loop(可能)
再大的事務提交的時間很短的原因:innodb儲存引擎仍然每秒會將重做日誌緩衝中的內容重新整理到重做記錄檔
合并插入緩衝 也不是每秒都會發生的 innodb儲存引擎會判斷當前一秒內發生的io次數是否小於5次 如果小於5次 innodb人為當前io壓力很小 可以執行合并插入緩衝的操作
buf_get_modified_ratio_pct跟百分之90的innodb_max_dirty_pages_pct相比較 超過 才會人為需要進行磁碟同步操作 將100個髒頁寫入磁碟
每10秒的操作
1 重新整理100個髒頁到磁碟(可能)
2 合并之多5個插入緩衝(總是)
3 將日誌緩衝重新整理到磁碟(總是)
4 刪除無用的undo頁(總是)
5 重新整理100或者10個髒頁到磁碟(總是)
過程
1 判斷10秒之內的磁碟的IO操作是否小於200
2 小於 innodb認為有足夠的IO能力 將100的髒頁重新整理到磁碟
3 innodb儲存引擎會合并插入緩衝 這次合并插入緩衝總會在這個剪短進行
4 innodb儲存引擎會再進行一次獎入職緩衝重新整理到磁碟的操作 這和沒秒一次發生的操作一樣
5 innodb進一步執行full purge 即刪除無用的undo頁 對錶update delete這類操作時操作時 原先的行被標記為刪除 但是因為一致性讀(consistent read)的關係 需要保留這些行版本的資訊 但是在full purge過程中 innodb儲存引擎會判斷當前事物系統已被刪除的行是否可以刪除 比如有時候還有查詢操作需要讀取之前版本的undo資訊 如果可以刪除 innodb會立即將其刪除
6 buf_get_modified_ratio_pct >70% 則重新整理100個髒頁到磁碟 <70%重新整理10%的髒頁到磁碟
background loop 噹噹前沒有使用者活躍(資料庫空閑)或者資料庫關閉(shutdown)
進行的操作
1 刪除無用的undo(總是)
2 合并20個插入緩衝(總是)
3 跳回到主迴圈(總是)
4 不斷重新整理100個頁知道符合條件(可能 跳轉到flush loop中完成)
跳轉到flush loop 後 如果也沒什麼事情做了 innodb儲存引擎會切換到suspend loop 將master thread掛起 等待事件的發生 若使用者啟用了(enable)innodb 卻沒有使用任何innodb的表 那麼master thread 一直掛起狀態
innodb 1.2.x之前的master thread
參數 innodb_io_capacity 用來表示磁碟io的輸送量 預設是200 對於重新整理到磁碟頁的數量 按照innodb_io_capacity的百分比控制
1 在合并插入緩衝時 合并插入緩衝的數量為 innodb_io_capacity
2 在從緩衝區重新整理髒頁時 重新整理髒頁的數量為innodb_io_capacity
用來解決寫入密集的應用程式中 產生大於20額插入緩衝的情況 master thread忙不來的問題
參數 innodb_max_dirty_pages_pct
