MySQL
InnoDB
ROW_FORMAT

Row size too large (> 8126). が出たので、解決がてら周辺知識をまとめてみた

More than 2 years have passed since last update.

環境

mysql 5.6

現象

大量のTEXT型のカラムに対してデータをいれる際に下記のエラーがMysqlによって吐かれた。

mysql
Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help

解決方法

エラー文にかかれているように
ROW_FORMAT=DYNAMICROW_FORMAT=COMPRESSEDをテーブルに設定してあげればOK!

CREATE TABLE `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hoge1` TEXT NOT NULL AUTO_INCREMENT,
  `hoge2` TEXT NOT NULL AUTO_INCREMENT,
   :
   :
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

注意点

上記だけだと正しく反映されない場合がある。
それはSHOW TABLE STATUS LIKE [対象テーブル名]\Gで確認できる。
ROW_FORMATが指定したものになっていれば良いが出来ていない場合がある。
その際は下記の設定をmy.cnfにする必要がある。
my.cnfがどこ?という場合は右記で確認 mysql --help | grep my.cnf

my.cnf
[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix

再起動後に先ほどのcreate文を流せば反映されるはず。

そもそもの話

my.cnfに指定したBarracudaとは?

innodbにはAntelopeとBarracudaとあり、Barracudaのほうが新しいフォーマット
違いとしては
Antelope
可変長カラム(VARBINARY, VARCHAR, BLOB, TEXT)の先頭768バイトを B-tree ノードのインデックスレコードに格納し、残りをオーバーフローページに格納する。

Barracuda
可変長カラム(VARBINARY, VARCHAR, BLOB, TEXT)の全てを外部のオーバーフローページに格納し、クラスタインデックスレコードにそのページへのポインタ(20B)のみを格納する。

つまりBarracudaはポインタのみを格納するためより多くの文字列を格納できる!

ROW_FORMATにDynamicとかCompressedって何?

ROW_FORMATの種類は現状4種類!

ROW_FOMATの種類 何者? 対応innodb_file_format
Redundant かつて使われていたもの Antelope
Compact myql5.0から追加されUTF8の最適化やRedundantよりもデータサイズが小さくなる。 Antelope
Dynamic データの圧縮を行わない Barracuda
Compressed データを圧縮しデータサイズを縮小してI/O現象の効果 Barracuda

何も指定しないとCompactになります。
さきほどの注意事項でBarracudaを設定しないとDynamicとCompressedを設定できなかったのも
この対応表を見ればすっきり!

DynamicとCompressedどっちを選択?

Dynamicはデータをgzipで圧縮するようで、その分スループットが落ちるそうなので、
パフォーマンスを気にしないならCompressedかな?

おまけ

RailsのMigrationでROW_FORMATを指定

create_table :table_name, options: 'ROW_FORMAT=DYNAMIC' do |t|
## カラム
end