innodb_rubyでfast index creationとそうでないのを比べてみる

や、MyNA会でデモした時にむしろこれをやれよって話なんですが。

InnoDB Plugin以降で加わったInnoDBのfast index creationと、それまでのインデックス作成について、innodb_rubyを使って比べてみました。

まずは最初にロードを済ませてから、後からfast index creationでインデックスを作るパターン。

master [localhost] {msandbox} (d1) > CREATE TABLE t1 (num int unsigned primary key, val varchar(32) not null);   
Query OK, 0 rows affected (0.02 sec)

master [localhost] {msandbox} (d1) > LOAD DATA INFILE '/tmp/md5_10000' INTO TABLE t1;                            
Query OK, 10000 rows affected (0.17 sec)
Records: 10000  Deleted: 0  Skipped: 0  Warnings: 0

$ innodb_space -f d1/t1.ibd space-extents-illustrate

  Start Page ╭────────────────────────────────────────────────────────────────╮
           0 │███▁▄███████████████████████████████░░░░░░░░░░░░░░░░░░░░░░░░░░░░│
          64 │█████░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░│
             ╰────────────────────────────────────────────────────────────────╯

Legend (█ = 1 page):
  Page Type                                                         Pages    Ratio
  █ System                                                              3    0.52%
  █ Index 31                                                           38    6.60%
  ░ Free space                                                         87   15.10%

ウインドーズだと文字化けしそうな予感がたっぷりなので、xtermでやったスクリーンショットもぺたり。

コイツにインデックスを足します。

master [localhost] {msandbox} (d1) > ALTER TABLE t1 ADD KEY (val);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ innodb_space -f d1/t1.ibd space-extents-illustrate                        [66/19159]

  Start Page ╭────────────────────────────────────────────────────────────────╮
           0 │███▁▄███████████████████████████████▁▅█████████████████████████▆│
          64 │█████░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░│
             ╰────────────────────────────────────────────────────────────────╯

Legend (█ = 1 page):
  Page Type                                                         Pages    Ratio
  █ System                                                              3    0.52%
  █ Index 31                                                           38    6.60%
  █ Index 32                                                           28    4.86%
  ░ Free space                                                         59   10.24%

やっぱり白黒になりやがったか。。
スクリーンショットでぺたり。

もともとあったデータ領域の後ろに、新しく作られたインデックスが追加されています。

じゃあインデックスを追加してからロードするパターン。

master [localhost] {msandbox} (d1) > CREATE TABLE t2 (num int unsigned primary key, val varchar(32) not null, key
Query OK, 0 rows affected (0.01 sec)

master [localhost] {msandbox} (d1) > LOAD DATA INFILE '/tmp/md5_10000' INTO TABLE t2;
Query OK, 10000 rows affected (0.30 sec)
Records: 10000  Deleted: 0  Skipped: 0  Warnings: 0

[mysql@v157-7-154-209 data]$ innodb_space -f d1/t2.ibd space-extents-illustrate                                  

  Start Page ╭────────────────────────────────────────────────────────────────╮
           0 │███▁▁▄█▆▇██▇███▇██▇▇████▇██▇▇▇▆█▆▆████████████▇▇▇▇██▆▇█▇▇█▇▇█▆▆█│
          64 │█████░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░│
         128 │█████░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░│
         192 │░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░│
         256 │░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░│
             ╰────────────────────────────────────────────────────────────────╯

Legend (█ = 1 page):
  Page Type                                                         Pages    Ratio
  █ System                                                              3    0.47%
  █ Index 33                                                           38    5.94%
  █ Index 34                                                           33    5.16%
  ░ Free space                                                        246   38.44%

データ本体(Index 33)とvalインデックス(Index 34)が交じり合って並ぶような感じ。


最後に、ALGORITHM= COPYでインデックスを追加した場合。

master [localhost] {msandbox} (d1) > CREATE TABLE t3 (num int unsigned primary key, val varchar(32) not null);   
Query OK, 0 rows affected (0.05 sec)

master [localhost] {msandbox} (d1) > LOAD DATA INFILE '/tmp/md5_10000' INTO TABLE t3;
Query OK, 10000 rows affected (0.17 sec)
Records: 10000  Deleted: 0  Skipped: 0  Warnings: 0

master [localhost] {msandbox} (d1) > ALTER TABLE t3 ADD KEY (val), ALGORITHM= COPY;
Query OK, 10000 rows affected (0.20 sec)
Records: 10000  Duplicates: 0  Warnings: 0

$ innodb_space -f d1/t3.ibd space-extents-illustrate                        [16/19155]

  Start Page ╭────────────────────────────────────────────────────────────────╮
           0 │███▁▄███████████████████████████████░░░░░░░░░░░░░░░░░░░░░░░░░░░░│
          64 │█████░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░│
             ╰────────────────────────────────────────────────────────────────╯

Legend (█ = 1 page):
  Page Type                                                         Pages    Ratio
  █ System                                                              3    0.52%
  █ Index 35                                                           38    6.60%
  ░ Free space                                                         87   15.10%

$ innodb_space -f d1/t3.ibd space-extents-illustrate                         [2/19155]

  Start Page ╭────────────────────────────────────────────────────────────────╮
           0 │███▁▁▄█▆▇██▇███▇██▇▇████▇██▇▇▇▆█▆▆████████████▇▇▇▇██▆▇█▇▇█▇▇█▆▆█│
          64 │█████░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░│
         128 │█████░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░│
         192 │░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░│
         256 │░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░│
             ╰────────────────────────────────────────────────────────────────╯

Legend (█ = 1 page):
  Page Type                                                         Pages    Ratio
  █ System                                                              3    0.47%
  █ Index 36                                                           38    5.94%
  █ Index 37                                                           33    5.16%
  ░ Free space                                                        246   38.44%

おお、やっぱりこうなるよね。


なお、t1テーブル(ロードしてからADD INDEX)をOPTIMIZEしても特に変わらなかった。
ALTER TABLE t1 Engine= InnoDB相当で、暗黙にALGORITHM= COPYだからだと思われる。ALTER TABLE t1 Engine= InnoDBは変わらなかったけれど、ALTER TABLE t1 Engine= InnoDB, ALGORITHM= COPYだと2つのインデックスが並ぶ感じに整列された。

innodb_ruby楽しいよ！