かみぽわーる

kamipo's blog

InnoDBの制限とファイルフォーマットAntelopeとBarracudaの違い

この投稿はMySQL Casual Advent Calendar 2014の5日目の記事です。

先週ツイッターでInnoDBのことを質問されまして、せっかくなのでアドカレのネタにしようと思いますってことでInnoDBのファイルフォーマット毎の違いをカジュアルに説明しようと思います。

InnoDBのファイルフォーマットBarracudaと新機能

InnoDBにはファイルフォーマットとして昔からあるAntelopeと新しいフォーマット(5年も前からあるので新しくはないが)のBarracudaがあって、新しいフォーマットのBarracudaにはいろいろ改良や新機能が追加されています。

例えば、ROW_FORMAT=COMPRESSEDとかは分かりやすい新機能で、上のCOMPRESSEDではなくDYNAMICを選んでいる理由を質問されたときには、とりいそぎ圧縮展開するため最大スループットが落ちますという回答をしたんですけど、COMPRESSEDの性能については@さんのエントリが参考になります。

他には、innodb_large_prefixによるインデックスサイズ制限の拡張もBarracudaの新機能です。

もうひとつ、見た目地味だけどドキュメントには書いてある大きな違いがあって、それは可変長カラム(VARBINARY, VARCHAR, BLOB, TEXT)の扱いについてです。

可変長カラム(VARBINARY, VARCHAR, BLOB, TEXT)の扱いの違い

ざっくりいうと、従来のAntelopeでは可変長カラムの値は先頭768バイトはローカルページに保存して残りの部分を外部のオーバーフローページに保存するけど、Barracudaは可変長カラムの値をすべて外部のページに保存してローカルページにはそのページへの20バイトのポインタだけ保存します。

これは実際どういう効果が期待できるかという話なんですけど、サイズの大きい可変長カラムをクラスタインデックスから追い出して行のサイズを小さく保てるということは、ページあたりより多くの行を詰め込めるようになるということで、バッファプールにより多くの行を乗せられるようになることを期待できると思います。

また、InnoDBには1つの行の最大サイズはページサイズの半分(innodb_page_sizeのデフォルトは16KBなので約8000バイト)までという制限があるので、Antelopeだと例えばutf8でvarchar(255)のカラムが11個あるテーブルに最大サイズまでデータを入れようとするとエラーになるんですけど、Barracudaだとローカルページにはポインタしか保持してないので余裕で保存できたりします。

CREATE TABLE tbl_compact (
    col01 varchar(255),
    col02 varchar(255),
    col03 varchar(255),
    col04 varchar(255),
    col05 varchar(255),
    col06 varchar(255),
    col07 varchar(255),
    col08 varchar(255),
    col09 varchar(255),
    col10 varchar(255),
    col11 varchar(255)
) CHARACTER SET utf8 ROW_FORMAT=COMPACT;

CREATE TABLE tbl_dynamic (
    col01 varchar(255),
    col02 varchar(255),
    col03 varchar(255),
    col04 varchar(255),
    col05 varchar(255),
    col06 varchar(255),
    col07 varchar(255),
    col08 varchar(255),
    col09 varchar(255),
    col10 varchar(255),
    col11 varchar(255)
) CHARACTER SET utf8 ROW_FORMAT=DYNAMIC;

# error
$ perl -le 'print "INSERT INTO tbl_compact VALUES (",join(q{,},map{q{"}.("あ"x255).q{"}}1..11),")"' | mysql -u root test
ERROR 1118 (42000) at line 1: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
Error (Code 1118): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
Error (Code 1030): Got error 139 from storage engine

# ok
$ perl -le 'print "INSERT INTO tbl_dynamic VALUES (",join(q{,},map{q{"}.("あ"x255).q{"}}1..11),")"' | mysql -u root test
参考