MySQL InnoDBで8KBの壁に阻まれエラー

2016.09.03

MySQL InnoDB 8KBの壁をご存知でしょうか。今回その壁で大いにハマったので備忘録です。

まずはハマった状況

いつも使っているMySQLのテーブルで、いつものようにUPDATE文を実行したのですが、MySQLから見たことの無いエラーが返ってきました。メッセージはこんな感じ。

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

このエラーメッセージについてGoogle先生に聞いてみると「InnoDBの8KB問題」が複数ヒット、内容も今回のエラー原因と一致していそうです。

なんでも、InnoDBでは1行が8KBを超えるとエラーになるらしく、これが「InnoDB 8KBの壁」と呼ばれるそうです。ふむふむ。なるほどなるほど、そりゃー大変だ!

ところで、InnoDBってなんだっけ?

軽く読んだだけじゃ分かんない!

疑問を整理しよう

対応方法は簡単ですが、意味を理解しないまま作業は出来ません。理解せず作業するのは、右も左も分からない新人に顧客データのたっぷり詰まった本番環境のアイパスを渡すくらい危険な行為だって先輩が言ってたような、なかったような。

とは言っても、やみくもに調べると効率が悪いので小さなことからコツコツと順を追って調べます。

そもそもInnoDBって何だっけ

InnoDBはMySQLのストレージエンジンだそうです。ストレージエンジンとは、MySQLやPostgreSQLなどのデータベース管理システムが、データベースにデータを書き込んだり読み込んだりするときに使われる基盤のことです。

InnoDB以外のストレージエンジンで有名なのはMyISAMでしょうか。たまーに動いているのを見かけます。MySQL5.5以降はInnoDBがデフォルトになったそうで、今回エラーを吐いたMySQLもInnoDBでした。

なんで1行 8KBなの

上の方で「InnoDBでは1行最大8KB」と書きましたが、この8KBは何処から出てきた数値でしょう?それはページサイズの半分だそうです。InnoDBではデフォルトのページサイズが16KBになっていて、その半分だから8KB。もし仮にページサイズを8KBにしたなら、行の最大は4KBと言うことになります。

The maximum row length, except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is slightly less than half of a database page for 4KB, 8KB, 16KB, and 32KB innodb_page_size settings.

へーなるほど。ふんふん。

で、ページってなに?

ページとは

InnoDB がディスク (データファイル) とメモリー (バッファープール) 間で一度に転送するデータ量を表す単位。ページには 1 つ以上の行を含めることができます (各行のデータ量に依存)。

ややこしい!ページはMySQLに限らず他のリレーショナルデータベースでも使われている基本単位だそうです。詳しくは以下の参考URLをご覧ください。

原因を整理

用語や概念の理解を深めたところでエラーの話に戻ります。InnoDBでは8KBを超えた行を操作(今回はUPDATE)するとエラーになる、と言うことは分かりました。

そこで、1つ疑問が浮かび上がってきます。私そんな大量のデータ入れようとしてたかしら?

TEXTお前が犯人だ

大したデータをUPDATEしようとしていないのに、サイズが8KBを越える原因はTEXT型のカラムでした。InnoDBでは、TEXT型など可変長の文字列型に値を入れると先頭768バイトだけローカルに保持して残りは外部のページに保持するそうな。

言葉じゃ分かり難いので計算してみましょう。

8000バイト÷768バイト=約10.42

と言うわけで、可変長のカラムのうち11カラム以上に値が入ると8KBを越してしまうようです。

私がUPDATEしようとしていたテーブルはTEXT型が20以上有りまして、今回はその全てに値を入れようとしていました。そらエラーにもなるで。

対応方法を選定

エラーの原因が理解できたところで対応方法を考えます。…なんて偉そうに書いてみましたが、対応方法を詳しく解説しているサイトが沢山ありますので、対応方法は調べて選ぶだけの簡単なお仕事です。

具体的な対応方法ですが、ROW_FORMATをDYNAMICに変更して対応しようと思います。エラーメッセージにも以下のように書かれているのでおすすめなんでしょう。…たぶん。

Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help.

作業手順

ROW_FORMATをDYNAMICに変更するためには、いくつかの手順を踏む必要が有ります。

InnoDB Plugin用の設定を追加

まずはInnoDB Pluginが入っているか調べます。コマンド

select @@innodb_version;

を実行してバージョンが表示されればInnoDB Plugin入ってます。その場合は不要なので、次項まで飛ばしてください。

mysql> select @@innodb_version;
+------------------+
| @@innodb_version |
+------------------+
| 5.1.73           |
+------------------+
1 row in set (0.00 sec)

もし表示されなければ、InnoDB Pluginが入っていないと言うことなので以下を追記しましょう。

 // my.cnf
[mysqld]  
ignore-builtin-innodb  
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

追記したらMySQLを再起動してバージョンが表示されるか確認しましょう。

innodb_file_formatを変更

InnoDB Pluginが入ったら、次は以下の2行を設定ファイルへ追記します。

 // my.cnf
innodb_file_per_table
innodb_file_format=Barracuda

追記が終わったら、またまたMySQLを再起動します。InnoDB Pluginと合わせて一気に書いても良いんですけど、細分化して作業する方が失敗したとき原因特定しやすいんですよね。私はチマチマやるのが好きです。

再起動が終わったら以下のコマンドを実行してBarracudaが有効になっているか確認します。

mysql> SHOW GLOBAL VARIABLES LIKE '%innodb_file_%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
3 rows in set (0.00 sec)

よし、変わった!

オマケのように書かれているinnodb_file_per_tableは、テーブルごとにテーブルスペースを分けるための設定だとか。

いよいよDYNAMICを設定

ここまで終わって、ようやくDYNAMICの設定が出来ます。設定はいたって簡単、以下のコマンドを打ちましょう。

mysql> ALTER TABLE `table_test` ROW_FORMAT=DYNAMIC;
Query OK, 765 rows affected (0.18 sec)
Records: 765  Duplicates: 0  Warnings: 0
mysql> SHOW TABLE STATUS LIKE 'table_test'\G
*************************** 1. row ***************************
Name: table_test
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 761
Avg_row_length: 2088
Data_length: 1589248
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: NULL
Create_time: 2016-09-02 22:04:30
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: row_format=DYNAMIC
Comment:
1 row in set (0.00 sec)

…3回くらい実行して、ようやく変わってくれました。最後なのに!!最後くらいスッキリキッパリ終わらせて!!

とりあえずこれで完了です!以上の設定後、最初と同じUPDATE文を実行したら無事に更新してくれました。壁を突破できたぞー!良かったー!

まとめと疑問と反省と

ちなみに、なんでInnoDB Pluginを入れてあげなきゃいけないのかと言うと、ROW_FORMAT の DYNAMIC を設定するには innodb_file_format を Barracuda にしなくちゃいけなくて、そのためには InnoDB Plugin が必要だからです。
その証拠に、追加しないままinnodb_file_format に Barracudaを設定してMySQLを再起動したら起動で失敗するようになりました。たぶんInnoDB Pluginから追加されたFile format managementあたりが関係しているのかなーと。これが無いとinnodb_file_formatが使えないとか。

技術系の記事を書いていて思うのですが、人に理解して貰えるように書くって驚くほど難しいです。分かり難くて本当に申し訳ないです。

参考サイト

InnoDB Pluginことはじめ。快適ストレージエンジン生活はじまる! / 漢のコンピュータ道
InnoDBの8KBの壁にぶち当たったら。 / SAWARA.ME

参考にしたサイトの皆さま、大変お世話になりました。素晴らしい情報を有難うございました!!

関連する記事

CATEGORY

ARCHIVE

NEW