InnoDB の主な機能 - 挿入キャッシュ、2 度書き込み、適応ハッシュ インデックスの詳細

InnoDB ストレージ エンジンの主な機能には、挿入バッファ、二重書き込み、適応ハッシュインデックスなどがあります。これらの機能により、InnoDB ストレージ エンジンのパフォーマンスと信頼性が向上します。

バッファを挿入

挿入バッファリングは、InnoDB ストレージ エンジンの主要機能の中で最も魅力的なものです。ただし、名前から判断すると、挿入バッファはバッファ プールの一部であると考えられるかもしれません。実際はそうではありません。Insert Buffer 情報を InnoDB バッファ プールに持つのは良いことですが、Insert Buffer もデータ ページと同様に物理ページのコンポーネントです。

主キーは行の一意の識別子です。行レコードは、アプリケーション内の主キーの昇順で挿入されます。したがって、クラスター化インデックスへの挿入は通常は順次行われ、ディスクからのランダム読み取りは必要ありません。

たとえば、次の SQL に従ってテーブルを定義します: create table t (id int auto_increment, name varchar (30), primary key (id));

id 列は自動的に増加します。つまり、挿入操作が実行されると、id 列が自動的に増加し、ページ内の行レコードは id が実行された順序で格納されます。通常、レコードの保存を実行するために別のページをランダムに読み取る必要はありません。したがって、このような場合には、挿入操作は通常すぐに完了します。ただし、各テーブルにクラスター化インデックスを 1 つだけ持つことは不可能です。多くの場合、テーブルには複数の非クラスター化セカンダリ インデックスが存在します。たとえば、名前フィールドに従って検索する必要もありますが、名前フィールドは一意ではありません。

テーブルは次の SQL ステートメントによって定義されます: create table t (id int auto_increment, name varchar(30), primary key(id), key(name));

これにより、クラスター化されていない、一意でないインデックスが作成されます。挿入操作を実行すると、データ ページは主キー id の実行順序で保存されますが、非クラスター化インデックスの場合、リーフ ノードの挿入は順番ではなくなります。このとき、非クラスター化インデックス ページへの個別のアクセスが必要となり、挿入パフォーマンスが低下します。ただし、これは名前フィールドのインデックスの障害ではありません。B+ ツリーの特性によって、非クラスター化インデックスの挿入の離散性が決まるためです。

InnoDB ストレージ エンジンは、挿入バッファを革新的に設計しました。非クラスター化インデックスの挿入または更新操作では、毎回インデックス ページに直接挿入するのではなく、挿入された非クラスター化インデックス ページがバッファ プールにあるかどうかを最初に判断します。存在する場合は直接挿入されます。存在しない場合は、まず挿入バッファーに配置され、この非クラスター化インデックスがリーフノードに挿入されたとデータベースを欺き、次に挿入バッファーと非クラスター化インデックスページの子ノードのマージ操作が一定の頻度で実行されます。このとき、複数の挿入は通常 1 つの操作にマージできます(1 つのインデックスページ内にあるため)。これにより、非クラスター化インデックスに対する挿入および変更操作のパフォーマンスが大幅に向上します。

挿入バッファリングを使用するには、次の 2 つの条件を満たす必要があります。

1. インデックスはセカンダリインデックスです。

2. インデックスが一意ではありません。

上記の 2 つの条件が満たされると、InnoDB ストレージ エンジンは挿入バッファリングを使用し、パフォーマンスが向上します。ただし、アプリケーションが多数の挿入操作と更新操作を実行し、そのすべてが一意でない非クラスター化インデックスに関係している状況を考えてみましょう。このプロセス中にデータベースがクラッシュすると、大量の挿入バッファーが実際の非クラスター化インデックスにマージされません。この場合、マージ復元操作を実行するには、回復に長い時間がかかり、極端な場合には数時間かかることもあります。

補助インデックスは、挿入バッファに挿入するときにインデックス ページの検索を行わないため、一意にすることはできません。検索すると、必ず個別の読み取りが行われ、バッファを挿入しても意味がなくなります。

挿入バッファ情報を表示します。

エンジン InnoDB ステータスを表示\G

seg size は現在の挿入バッファ サイズが 2*16KB であることを示し、free list len はフリー リストの長さを表し、size はマージされたレコード ページの数を表します。

次の行は、パフォーマンスの向上を示しているため、おそらく私たちが本当に気にしている部分です。 inserts は挿入されたレコードの数を表し、merged recs はマージされたページの数を表し、merges はマージの数を表します。

マージされたレコードとマージの比率は約 3:1 です。つまり、挿入バッファリングによって、非クラスター化インデックス ページの IO 要求が約 3 分の 1 に削減されます。

質問：

現在、挿入バッファリングに問題があります。書き込みが集中する状況では、挿入バッファリングによってバッファ プール メモリが大量に消費されます。デフォルトでは、バッファ プール メモリの最大 1/2 を消費します。 Percona は、挿入バッファがバッファ プール メモリを過剰に占有する問題を修正するパッチをいくつかリリースしました。詳細については、http://www.percona.com/percona-lab.html を参照してください。簡単に言えば、IBUF_POOL_SIZE_PER_MAX_SIZE を変更することで、挿入バッファのサイズを制御できます。たとえば、IBUF_POOL_SIZE_PER_MAX_SIZE を 3 に変更すると、バッファ プール メモリの最大 1/3 しか使用できなくなります。

2回書く

挿入バッファリングがInnoDB ストレージ エンジンにパフォーマンスをもたらすのであれば、二重書き込みは InnoDB ストレージ エンジンにデータの信頼性をもたらします。データベースがクラッシュすると、データベースがページを書き込んでいるものの、ページの一部のみが書き込まれることがあります (たとえば、16K ページの場合、ページの最初の 4K のみが書き込まれます)。これを部分ページ書き込みと呼びます。 InnoDB ストレージ エンジンが二重書き込みテクノロジを使用する前は、部分的な書き込み失敗によりデータが失われるケースがありました。

書き込み障害が発生した場合、REDO ログを通じて回復できると考える人もいるかもしれません。これは一つの方法です。ただし、 REDO ログには、オフセット 800 や 'aaaa' レコードの書き込みなど、ページ上の物理的な操作が記録されていることは明らかです。ページ自体が破損している場合は、やり直しても意味がありません。つまり、REDO ログを適用する前に、ページのコピーが必要です。書き込み障害が発生すると、ページはまずページのコピーを通じて復元され、その後、REDO されます。これが二重書き込みです。

InnoDB ストレージ エンジンの二重書き込みのアーキテクチャを図 2-4 に示します。

ダブルライトは 2 つの部分で構成されます。1 つはメモリ内のダブルライト バッファで、サイズは 2 MB です。もう 1 つは物理ディスク上の共有テーブルスペース内の連続する 128 ページ、つまり 2 つのエクステントで、サイズも 2 MB です (ページのコピー) 。バッファ プールのダーティ ページが更新されると、そのダーティ ページはディスクに直接書き込まれるのではなく、memcpy 関数によってメモリ内のダブルライト バッファにコピーされます。次に、ダブルライト バッファによって共有テーブルスペースの物理ディスクに 1 MB ずつ 2 回書き込まれます。その後、バッファ書き込みによって発生する問題を回避するために、fsync 関数がすぐに呼び出され、ディスクが同期されます。このプロセスでは、二重書き込みページが連続しているため、プロセスは順番に書き込まれ、オーバーヘッドはそれほど大きくありません。ダブルライト ページの書き込みが完了すると、ダブルライト バッファ内のページが各テーブルスペース ファイルに書き込まれます。このとき、書き込みは個別です。

次のコマンドを実行すると、二重書き込み操作を観察できます: show global status like 'innodb_dblwr%'\G

Doublewrite では合計 18,445 ページが書き込まれますが、実際の書き込み数は 434 (42:1) であり、基本的に 64:1 と一致します。

ピーク時間帯に Innodb_dblwr_pages_written:Innodb_dblwr_writes が 64:1 より大幅に小さい場合、システムへの書き込み負荷がそれほど高くないことを意味します。

ページをディスクに書き込むプロセス中にオペレーティング システムがクラッシュした場合、リカバリ プロセス中に、InnoDB ストレージ エンジンは共有テーブルスペース内の二重書き込みから変更されたページのコピーを見つけ、それをテーブルスペース ファイルにコピーし、 REDO ログを適用します。以下は、ダブルライトによる回復のケースを示しています。

090924 11:36:32 mysqldが再起動しました
090924 11:36:33 InnoDB: データベースは正常にシャットダウンされませんでした。
InnoDB: クラッシュリカバリを開始しています。
InnoDB: .ibd ファイルからテーブルスペース情報を読み取っています……
InnoDB: エラー: スペース ID は fsp ヘッダー 0 にありますが、ページ ヘッダー 4294967295 にあります
InnoDB: エラー: ファイル ./test/t.ibd のテーブルスペース ID 4294967295 は適切ではありません
InnoDB: エラー: ファイル ./test/t2.ibd のテーブルスペース ID 0 は意味がありません
090924 11:36:33 InnoDB: ファイル操作でオペレーティング システム エラー番号 40 が発生しました。
InnoDB: エラー番号 40 は、「シンボリック リンクのレベルが多すぎます」という意味です。
InnoDB: 一部のオペレーティングシステムのエラー番号については、
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/operating-system-error-codes.html
InnoDB: ファイル名./now/member
InnoDB: ファイル操作呼び出し: 'stat'。
InnoDB: エラー: os_file_readdir_next_file() が -1 を返しました
InnoDB: ディレクトリ./now
InnoDB: 一部の .ibd ファイルのクラッシュ回復が失敗した可能性があります。
InnoDB: 二重書き込みから半分書き込まれた可能性のあるデータページを復元する
InnoDB: バッファ...

パラメータskip_innodb_doublewrite は二重書き込み機能を無効にすることができ、これにより上記の書き込み失敗の問題が発生する可能性があります。ただし、スレーブ サーバーが複数あり、より高速なパフォーマンス (スレーブ上の RAID0 など) を提供する必要がある場合は、このパラメーターを有効にすることが解決策になる可能性があります。ただし、高いデータ信頼性を提供する必要があるマスター サーバーでは、二重書き込み機能が常に有効になっていることを確認する必要があります。

注: 一部のファイル システムでは、ZFS ファイル システムなど、部分的な書き込みの失敗を防ぐメカニズムが提供されています。この場合、二重書き込みを有効にしないでください。

適応ハッシュインデックス

ハッシュは非常に高速な検索方法であり、検索時間の計算量は一般に O(1) です。 SQL Server や Oracle のハッシュ結合などの結合操作でよく使用されます。ただし、SQL Server や Oracle などの一般的なデータベースはハッシュ インデックスをサポートしていません。 MySQL のヒープ ストレージ エンジンのデフォルトのインデックス タイプはハッシュですが、InnoDB ストレージ エンジンでは、別の実装方法であるアダプティブ ハッシュ インデックスが提案されています。

InnoDB ストレージ エンジンは、テーブル上のインデックスの検索を監視します。ハッシュ インデックスを作成することで速度が向上することがわかった場合は、ハッシュ インデックスを作成するため、アダプティブと呼ばれます。適応ハッシュ インデックスは、バッファー プールの B+ ツリーから構築されるため、非常に高速に構築されます。また、テーブル全体のハッシュ インデックスを作成する必要はありません。InnoDB ストレージ エンジンは、アクセスの頻度とパターンに基づいて、特定のページのハッシュ インデックスを自動的に作成します。

InnoDB の公式ドキュメントによると、アダプティブ ハッシュ インデックスを有効にすると、読み取りと書き込みの速度が 2 倍に向上し、補助インデックスの接続操作ではパフォーマンスが 5 倍に向上します。アダプティブ ハッシュ インデックスは非常に優れた最適化モードです。その設計コンセプトはデータベースのセルフチューニングであり、DBA がデータベースを調整する必要がありません。

アダプティブハッシュインデックスの現在の使用状況を表示します: show engine innodb status\G

これで、アダプティブ ハッシュ インデックスのサイズ、使用状況、1 秒あたりのアダプティブ ハッシュ インデックス検索数など、アダプティブ ハッシュ インデックスの使用状況情報を確認できるようになりました。ハッシュ インデックスは、select * from table where index_col='xxx' などの等しい値の検索にのみ使用でき、範囲検索などの他の検索タイプには使用できません。したがって、ここではハッシュ以外の検索が行われます。ハッシュ インデックスの使用効率を大まかに把握するには、hash searching:non-hash searching コマンドを使用します。

アダプティブ ハッシュ インデックスは InnoDB ストレージ エンジンによって制御されるため、ここでの情報は参考用です。ただし、デフォルトで有効になっているパラメータ innodb_adaptive_hash_index を使用して、この機能を無効または有効にすることができます。

InnoDB の主要機能である挿入キャッシュ、2 度書き込み、アダプティブ ハッシュ インデックスに関する上記の記事は、エディターが皆さんと共有する内容のすべてです。皆さんの参考になれば幸いです。また、123WORDPRESS.COM をサポートしていただければ幸いです。