posted at 2017-12-20

MySQL InnoDBの領域管理

武蔵野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の中身を見てみましょう。Jeremy Cole氏が開発したinnodb_rubyというツールで、テーブルスペースの中身を可視化することができます。

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)
);

# 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

プライマリインデックスだけのシンプルなテーブルを使って、動作を追ってみましょう。

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の機能で可視化したものです。青色であるほど過去に更新されたことを、赤色であるほど最近更新されたことを示しています。

1つ目の共有エクステントについては、最初から全部のページを使い切らないような挙動になっています。InnoDBの設計思想までは把握できていないのですが、ここは後で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というパラメータによって拡張単位が指定されます。

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

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

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

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

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

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

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

InnoDBはあるエクステント内のページがすべて空になった場合、エクステント自体に空きフラグを立てます。空きエクステントが存在する場合、InnoDBはテーブルスペースを拡張して新たなエクステントを割り当てるよりも、空きエクステントの再利用を優先します。

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

動作例3：ランダムINSERT

InnoDBはランダム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は十分効率的」ということは覚えておいて損はないと思います。