Innodb三大特性之insert buffer
一、什麼是insert buffer
insert buffer是一種特殊的數據結構(B+ tree)並不是緩存的一部分,而是物理頁,當受影響的索引頁不在buffer pool時緩存 secondary index pages的變化,當buffer page讀入buffer pool時,進行合並操作,這些操作可以是 INSERT, UPDATE, or DELETE operations (DML)
最開始的時候只能是insert操作,所以叫做insert buffer,現在已經改叫做change buffer了
insert buffer 只適用於 non-unique secondary indexes 也就是說只能用在非唯一的索引上,原因如下
1、primary key 是按照遞增的順序進行插入的,異常插入聚族索引一般也順序的,非隨機IO
2 寫唯一索引要檢查記錄是不是存在,所以在修改唯一索引之前,必須把修改的記錄相關的索引頁讀出來才知道是不是唯一、這樣Insert buffer就沒意義了,要讀出來(隨機IO)
所以只對非唯一索引有效
二、insert buffer的原理
對於為非唯一索引,輔助索引的修改操作並非實時更新索引的葉子頁,而是把若干對同一頁面的更新緩存起來做,合並為一次性更新操 作,減少IO,轉隨機IO為順序IO,這樣可以避免隨機IO帶來性能損耗,提高數據庫的寫性能
具體流程
先判斷要更新的這一頁在不在緩沖池中
a、若在,則直接插入;
b、若不在,則將index page 存入Insert Buffer,按照Master Thread的調度規則來合並非唯一索引和索引頁中的葉子結點
Master Thread的調度規則
a、主動merger[innodb主線程定期完成,用戶線程無感知]
主動merge通過innodb主線程(svr_master_thread)判斷:若過去1s之內發生的I/O小於系統I/O能力的5%,則主動進行一次insert buffer的merge操作。merge的頁面數為系統I/O能力的5%,讀取采用async io模式。每10s,必定觸發一次insert buffer meger操作。meger的頁面數仍舊為系統 I/O能力的5%。
1)主線程發出async io請求,async讀取需要被merge的索引頁面
2)I/O handler 線程,在接受到完成的async I/O之後,進行merge
b 、被動merge[用戶線程完成,用戶能感受到meger操作帶來的性能影響]
1) insert操作,導致頁面空間不足,需要分裂(split)。由於insert buffer只針對單個頁面,不能buffer page split[頁已經在內存裡],因此引起頁面的被動meger。同理,update操作導致頁面空間不 足;purge導致頁面為空等。總之,若當前操作引起頁面split or merge,那麼就會導致被動merge;
2) insert操作,由於其它各種原因,insert buffer優化返回false,需要真正讀取page時,要進行被動merge。與一不同的是,頁在disk上,需要讀取到內存裡;
3)在進行insert buffer操作,發現insert buffer太大,需要壓縮insert buffer,這時需要強制被動merge,不允許 insert 操作進行。
三、insert buffer的內部實現
1、insert buffer的數據結構是一棵B+樹,在MySQL4.1之前的版本中每張表都有一棵insert buffer B+樹
MySQL4.1之後,全局只有一棵insert buffer B+樹,負責對所有的表的輔助索引進行 insert buffer。這棵B+樹存放在共享表空間中,默認也就是ibdata1中。因此,試圖通過獨立表空間ibd文件恢復表中數據時,往往會導致check table 失敗。這是因為表的輔助索引中的數據可能還在insert buffer中,也就是共享表空間中。所以通過idb文件進行恢復後,還需要進行repair table 操作來重建表上所有的輔助索引
2、insert buffer的非葉子節點存放的是查詢的search key(鍵值),
其構造包括三個字段:space (4 byte)+ marker(1byte) + offset(4byte) = search key (9 byte )
space表示待插入記錄所在的表空間id,InnoDB中,每個表有一個唯一的space id,可以通過space id查詢得知是哪張表;
marker是用來兼容老版本的insert buffer;
offset表示頁所在的偏移量。
3、當一個輔助索引需要插入到頁(space, offset)時,如果這個頁不在緩沖池中,那麼InnoDB首先根據上述規則構造一個search key,接下來查詢insert buffer這棵B+樹,然後再將這條記錄插入到insert buffer B+樹的葉子節點中
4、對於插入到insert buffer B+樹葉子節點的記錄,需要根據如下規則進行構造:
space | marker | offset | metadata | secondary index record
啟用insert buffer索引後,輔助索引頁(space、page_no)中的記錄可能被插入到insert buffer B+樹中,所以為了保證每次merge insert buffer頁必須成功,還需要有一個特殊的頁來標記每個輔助索引頁(space、page_no)的可用空間,這個頁的類型為insert buffer bitmap。
四、insert buffer的缺點
1、可能導致數據庫宕機後實例恢復時間變長。如果應用程序執行大量的插入和更新操作,且涉及非唯一的聚集索引,一旦出現宕機,這時就有大量內存中的插入緩沖區數據沒有合並至索引頁中,導致實例恢復時間會很長
2、在寫密集的情況下,插入緩沖會占用過多的緩沖池內存(innodb_buffer_pool),默認情況下最大可以占用1/2,這在實際應用中會帶來一定的問題
3、insert buffer 無法進行控制,for different workloads and hardware configuration,特別是在SSD盛行的今天
五、查看insert buffer
mysql> show engine innodb status \G
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
41 inserts, 41 merged recs, 499 merges
Hash table size 3984403, node heap has 967 buffer(s)
14.66 hash searches/s, 64.65 non-hash searches/s
---
LOG
---
Log sequence number 27233311008
Log flushed up to 27233311008
Last checkpoint at 27233310593
0 pending log writes, 0 pending chkp writes
37848626 log i/o's done, 1.00 log i/o's/second
size: The number of pages used within the change buffer. Change buffer size is equal to seg size - (1 + free list len). The 1 + value represents the change buffer header page.
free list len: The number of pages free within the change buffer.代表了空閒頁的數量
seg size: The size of the change buffer, in pages. 插入緩沖的大小為2 *16KB
merges: The total number of change buffer merges.表示合並次數
merged operations - insert: The number of inserted records merged.merged插入的記錄數
merged operations - delete mark: The number of deleted records merged.merged刪除記錄數
merged operations - delete: The number of purge records merged.merged清除記錄數
discarded operations - insert: The number of insert merge operations discarded.
discarded operations - delete mark: The number of delete merge operations discarded.
discarded operations - delete: The number of purge merge operations discarded.