【mysql】Innodbの三大特性のinsert buffer
5581 ワード
一、insert bufferとは
Insert bufferは特殊なデータ構造(B+tree)であり、キャッシュの一部ではなく物理ページであり、影響を受けるインデックスページがbuffer poolでない場合はsecondary index pagesの変化をキャッシュし、buffer pageがbuffer poolを読み込む場合は、
最初は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)により、過去1 s以内に発生したI/OがシステムI/O能力の5%未満であれば、insert bufferのmerge操作をアクティブに1回行うと判断した.mergeのページ数はシステムI/O能力の5%であり,読み出しはasync ioモードを採用している.10 sごとに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を行う.1つとは異なり、ページはdisk上にあり、メモリに読み込む必要があります.
3)insert buffer操作を行うと,insert bufferが大きすぎてinsert bufferを圧縮する必要があることが分かったが,この場合は受動mergeを強制する必要があり,insert操作は許されない.
三、insert bufferの内部実現
1、insert bufferのデータ構造はB+ツリーで、MySQL 4である.1以前のバージョンでは各テーブルにinsert buffer B+ツリーがありました
MySQL4.1以降、グローバルには1本のinsert buffer B+ツリーしかなく、すべてのテーブルの補助インデックスに対してinsert bufferを担当します.このB+ツリーは共有テーブルスペースに格納され、デフォルトではibdata 1に格納されます.したがって、テーブル内のデータを独立した表領域ibdファイルで復元しようとすると、check tableが失敗することが多い.これは、テーブルのセカンダリインデックスのデータがinsert buffer、すなわち共有テーブル空間にある可能性があるためです.したがってidbファイルをリカバリした後、repair table操作を行ってテーブル上のすべての補助インデックスを再構築する必要があります.
2、insert bufferの非リーフノードはクエリーのsearch key(キー値)を格納し、
その構築には、space(4 byte)+marker(1 byte)+offset(4 byte)=search key(9 byte)の3つのフィールドが含まれます.
spaceは、レコードが挿入されるテーブル空間idを表し、InnoDBでは、各テーブルに一意のspace idがあり、space idクエリでどのテーブルであるかを知ることができる.
markerは古いバージョンと互換性のあるinsert bufferです.
offsetは、ページが存在するオフセットの量を表します.
3、補助インデックスをページに挿入する必要がある場合、このページがバッファプールにない場合、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の表示
リファレンス
https://dev.mysql.com/doc/refman/5.5/en/innodb-standard-monitor.html
https://dev.mysql.com/doc/refman/5.5/en/innodb-insert-buffering.html
https://www.percona.com/blog/2009/01/13/some-little-known-facts-about-innodb-insert-buffer/
http://blog.itpub.net/22664653/viewspace-1163838/
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)により、過去1 s以内に発生したI/OがシステムI/O能力の5%未満であれば、insert bufferのmerge操作をアクティブに1回行うと判断した.mergeのページ数はシステムI/O能力の5%であり,読み出しはasync ioモードを採用している.10 sごとに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を行う.1つとは異なり、ページはdisk上にあり、メモリに読み込む必要があります.
3)insert buffer操作を行うと,insert bufferが大きすぎてinsert bufferを圧縮する必要があることが分かったが,この場合は受動mergeを強制する必要があり,insert操作は許されない.
三、insert bufferの内部実現
1、insert bufferのデータ構造はB+ツリーで、MySQL 4である.1以前のバージョンでは各テーブルにinsert buffer B+ツリーがありました
MySQL4.1以降、グローバルには1本のinsert buffer B+ツリーしかなく、すべてのテーブルの補助インデックスに対してinsert bufferを担当します.このB+ツリーは共有テーブルスペースに格納され、デフォルトではibdata 1に格納されます.したがって、テーブル内のデータを独立した表領域ibdファイルで復元しようとすると、check tableが失敗することが多い.これは、テーブルのセカンダリインデックスのデータがinsert buffer、すなわち共有テーブル空間にある可能性があるためです.したがってidbファイルをリカバリした後、repair table操作を行ってテーブル上のすべての補助インデックスを再構築する必要があります.
2、insert bufferの非リーフノードはクエリーのsearch key(キー値)を格納し、
その構築には、space(4 byte)+marker(1 byte)+offset(4 byte)=search key(9 byte)の3つのフィールドが含まれます.
spaceは、レコードが挿入されるテーブル空間idを表し、InnoDBでは、各テーブルに一意のspace idがあり、space idクエリでどのテーブルであるかを知ることができる.
markerは古いバージョンと互換性のあるinsert bufferです.
offsetは、ページが存在するオフセットの量を表します.
3、補助インデックスをページに挿入する必要がある場合、このページがバッファプールにない場合、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*16 KB です.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. リファレンス
https://dev.mysql.com/doc/refman/5.5/en/innodb-standard-monitor.html
https://dev.mysql.com/doc/refman/5.5/en/innodb-insert-buffering.html
https://www.percona.com/blog/2009/01/13/some-little-known-facts-about-innodb-insert-buffer/
http://blog.itpub.net/22664653/viewspace-1163838/