MySQL
InnoDB
ROW_FORMAT

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

More than 3 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