武蔵野Advent Calendar 2017の20日目の記事です。品川から参加しています。

今日はMySQL InnoDBの領域管理について勉強し、いくつか動作例を見ながらInnoDBに対する理解を深めていきたいと思います。アプリケーション開発者やデータベース管理者の方にとって明日からすぐに使えるノウハウとまではいきませんが、いつか何かの役に立てば幸いです。

まとめ

• InnoDBにはテーブルスペース、セグメント、エクステント、ページというデータの管理単位があるよ
• エクステント単位で空き領域が管理されているよ。だけどそれを知ったところであまり役には立たないよ
• 昇順INSERTが得意でランダムINSERTが苦手なのはよく知られているけれど、実は降順INSERTが得意だよ

テーブルスペース、セグメント、エクステント、ページ

InnoDBのデータが格納されるファイルのことをテーブルスペースと呼びます。MySQL 5.6以降はパラメータinnodb_file_per_tableがデフォルトで有効化されたため、テーブルを1つ作成するごとにテーブルスペースが1つ作成されます。

# ls -l
合計 36916
-rw-r----- 1 27 27       65 12月 18 23:45 db.opt
-rw-r----- 1 27 27     8588 12月 20 01:28 demo1.frm
-rw-r----- 1 27 27  9437184 12月 20 17:33 demo1.ibd
-rw-r----- 1 27 27     8588 12月 20 17:22 demo2.frm
-rw-r----- 1 27 27 13631488 12月 20 17:33 demo2.ibd
-rw-r----- 1 27 27     8618 12月 20 18:15 demo3.frm
-rw-r----- 1 27 27 14680064 12月 20 18:15 demo3.ibd

この例ではテーブルがdemo1、demo2、demo3と3つあり、それぞれdemo1.ibd、demo2.ibd、demo3.ibdというテーブルスペースが作られています。なお、拡張子frmのファイルにはテーブル定義などのメタデータが格納されています。

demo3.ibdの中身を見てみましょう。

CREATE TABLE demo3 (
  id1 int(11) NOT NULL AUTO_INCREMENT,
  code varchar(64) NOT NULL,
  data varchar(64) NOT NULL,
  PRIMARY KEY (id1),
  KEY code (code)
);

Jeremy Cole氏が開発したinnodb_rubyというツールで、テーブルスペースの中身を可視化することができます。

# innodb_space -f demo3.ibd space-extents-illustrate

テーブルスペースはInnoDBの最小データ管理単位である16KBのページの集まりとして構成されています。さらに、連続する64個のページが1MBのエクステントとして管理されています。ページはSHOW ENGINE INNODB STATUSなどでよく見る用語ですが、エクステントは初めて見る方も多いのではないかと思います。

テーブルスペースの中にはテーブルやインデックスが格納されますが、このテーブルやインデックスのことを領域管理上はセグメントと呼びます。図ではテーブルスペース管理用のSystemを除くと、緑色と薄青色の2つのセグメントが格納されていることが分かるかと思います。緑色のセグメントがテーブル本体、薄青色のセグメントがcode列に対するセカンダリインデックスとなっています。InnoDBはクラスタインデックスを採用しているので、テーブル本体もインデックスとして扱われています。

エクステントはその状態ごとに共有エクステント、専有エクステント、空きエクステントと3つに分類することができます。共有エクステントには複数種類のセグメントのページを配置することができ、テーブルスペースの最初のエクステントは必ず共有エクステントとなります。一方で専有エクステントには単一のセグメントのみがページを配置することができます。空きエクステントはまだデータが格納されていないエクステントのことです。

注意：本記事では理解のし易さを優先して、説明の正確さについては妥協しています。詳しく知りたい方はJeremy Cole氏の記事やInnoDBのソースコードをご覧ください。

動作例1：昇順INSERT後、少しDELETEして、もう一度INSERT

プライマリインデックスだけのシンプルなテーブルを使って、動作を追いかけてみましょう。MySQLのバージョンは5.7.20です。

CREATE TABLE demo1 (
  id1 int(11) NOT NULL AUTO_INCREMENT,
  data varchar(500) NOT NULL,
  PRIMARY KEY (id1)
);

ID：1～10,000を昇順INSERT

虹色の図が増えていますが、これはページに格納されたLSN(Log Sequence Number)をinnodb_rubyの以下のオプションで可視化したものです。青色であるほど過去に更新されたことを、赤色であるほど最近更新されたことを示しています。

# innodb_space -f demo1.ibd space-lsn-age-illustrate

1つ目の共有エクステントについては、最初から全部のページを使い切らないような挙動になっています。この部分は後でINSERT/UPDATEによってページが分割されたり、インデックスを追加したりした際に使われることになります。

ID：3,001～4,000をDELETE

1,000行ほどDELETEすると、3つ目のエクステント(Page128～)にいくつか空きページができます。

ID：10,001～11,000を昇順INSERT

ここは少し特徴的な挙動ですね。先ほど空いたページを再利用するのではなく、7つ目のエクステント(Page384～)が確保されます。空きページを有効活用するよりも、インデックスのリーフページがディスク上で連続した配置になることを優先していると考えられます。

また、このタイミングでエクステントが10個目(Page576～)まで確保されます。innodb_file_per_tableを有効にして作成したテーブルスペースは、エクステントが4つ(4MB)ずつ拡張される仕様となっています。これとは別に、システムテーブルスペースについてはinnodb_autoextend_incrementというパラメータによって拡張単位が指定でき、デフォルトは64個(64MB)です。

ID：3,001～3,500を昇順INSERT

さきほどDELETEした行を復活させるような値でINSERTを行えば、空きページが再利用されます。

動作例2：昇順INSERT後、かなりDELETEして、もう一度INSERT

ID：1～10,000を昇順INSERT後、ID：2,501～7,000をDELETE

DELETEする行を増やしてみました。

ID：10,001～11,000を昇順INSERT

動作例1と異なる挙動になりました。7つ目のエクステントが確保されるのではなく、4つ目のエクステント(Page192～)が再利用されています。

InnoDBはあるエクステント内のページがすべて空になった場合、エクステント自体に空きフラグを立てます。空きエクステントが存在する場合、InnoDBはテーブルスペースを拡張して新たなエクステントを割り当てるよりも、空きエクステントの再利用を優先します。空きエクステントが再利用される状況においては、昇順INSERTに対して、エクステントを跨いだページ配置の連続性は維持されないことになります。

アプリケーション開発者やデータベース管理者の立場からは、テーブルスペースの利用効率を上げるためにエクステントの再利用を狙って発生させることなど到底無理なので、まあ、トリビアといったところですね。OSやストレージのチューニングに際して「昇順INSERTに対してページが連続配置されると期待できるのは、エクステント1つ(1MB)の範囲に限られる」ということは覚えておくと役に立つかもしれません。

動作例3：ランダムINSERT

InnoDBはランダムINSERTが苦手です。クラスタインデックスを採用しているので内部的にデータを整列した状態にしておく必要があるのですが、ランダムINSERTをすると入りきらなくなったページが分割されて追加の処理が発生してしまうためです。ランダムINSERTが多い状況では、CPU・I/O負荷の上昇や格納効率の低下が起きてしまうことになります。

ID：1～10,000をランダムINSERT

かなり分かりやすく可視化できていますね。データの格納効率が下がってしまい動作例1では6つで済んでいたエクステントが10個に増大していること、LSNについてほとんどのページが赤くなっており、多くのページを頻繁に更新し続けていることが分かります。

動作例4：降順INSERT

ID：8,001～10,000を降順INSERT

試してみるまで知らなかったのですが、降順INSERTは不思議な挙動をしています。共有エクステント内のページについてはこれまで通り前から順番に使っていくのですが、専有エクステント内のページをなんと後ろから順番に使っていきます。INSERTのときにこうやって工夫しておけば、SELECTのときには連続配置の恩恵を受けられるというわけです。またこれはInnoDB自身が、今自分が昇順INSERTをしているのか降順INSERTをしているのかを、ストレージエンジンの本来の役割を越えて把握しているということを表しています。面白いですね。

アプリケーション開発者やデータベース管理者の立場からは、「InnoDBにおいて降順INSERTは十分効率的」ということは覚えておいて損はないと思います。

まとめ

• InnoDBにはテーブルスペース、セグメント、エクステント、ページというデータの管理単位があるよ
• エクステント単位で空き領域が管理されているよ。だけどそれを知ったところであまり役には立たないよ
• 昇順INSERTが得意でランダムINSERTが苦手なのはよく知られているけれど、実は降順INSERTが得意だよ

innodb_rubyを使ってInnoDBの内部動作を紐解いていくのはなかなか面白いです。みなさんも年末年始に是非試してみてください。