InnoDBの8KBの壁にぶち当たったら。

InnoDBの行の最大長は約8KBらしい。

意外と少ない。。。

運用中のサービスがこんなエラーを吐いていました。。。

Got error 139 from storage engine

マジですか。これが噂の「InnoDB 8KBの壁」ですか。。。

設計段階であればテーブル縦分割とかテーブル構造自体を変えちゃえ!ってなるかもしれないですが、運用中のサービスですし、できるだけ全体へのインパクトは少なくしたい(アプリケーションは改修したくない)。って時にテーブルのROW_FORMATを変更して対応しましたよ、って話です。

「ROW_FORMAT=DYNAMIC」または「ROW_FORMAT=COMPRESSED」を使おう!

そうです、結論から言ってROW_FORMATを変更することで対応したんです。

ROW_FORMATについてMySQLリファレンス・マニュアルにこんなことが書いて有りました。

When a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, long column values are stored fully off-page, and the clustered index record contains only a 20-byte pointer to the overflow page.

「ROW_FORMAT=DYNAMIC」または「ROW_FORMAT=COMPRESSED」でテーブルを作成した時、長いカラムの値はすべて外部ページに保存され、クラスタインデックスレコードがそのオーバーフローページの20バイトのポインタを持ちます。

引用元:MySQL 5.6 Manual : 14.9.3 DYNAMIC and COMPRESSED Row Formats

アレですか!ここでいう「長いカラム」っていうのはあの、、VARCHARとかTEXTとか、その、アレのことですか!?

Early versions of InnoDB used an unnamed file format (now called Antelope) for database files. With that file format, tables are defined with ROW_FORMAT=COMPACT or ROW_FORMAT=REDUNDANT. InnoDB stores up to the first 768 bytes of variable-length columns (such as BLOB and VARCHAR) in the index record within the B-tree node, with the remainder stored on the overflow pages.

初期のバージョンのInnoDBはデータベースファイル用に名前の無いファイルフォーマット(今では”Antelope”と呼ばれる)ものが使用されていました。そのファイルフォーマットを使用したテーブルは「ROW_FORMAT=COMPACT」または「ROW_FORMAT=REDUNDANT」が定義されます。InnoDBは可変長カラム(BLOBやVARCHARのような)の先頭768バイトをB-treeノードのインデックスレコードに格納し、残りはオーバーフローページに格納されます。

引用元:MySQL 5.6 Manual : 14.9.4 COMPACT and REDUNDANT Row Formats

多分この可変長カラムが20バイトになるっていうことですよね!

イヤッッホォオォオウ!!

ではROW_FROMAT変更でエラー回避出来るか検証いきます!

そもそもエラーが発生する原因

そもそもエラーが発生する原因なんですが、ファイルフォーマットが”Antelope”の場合、上記の通り可変長のカラムは先頭768バイトが保存されるので、可変長カラムをふんだんに使っちゃうと8KBの壁にぶつかってしまうわけです。

MySQL5.6だとデフォルトのファイルフォーマットは”Antelope”になっているっていう。

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

で、この可変長のテーブルが11個以上あると8K < (768x11)になってエラーになる可能性が出てくるわけです。

それでは検証用テーブルを作成します。

CREATE TABLE `t1` (
    c01 TEXT,
    c02 TEXT,
    c03 TEXT,
    c04 TEXT,
    c05 TEXT,
    c06 TEXT,
    c07 TEXT,
    c08 TEXT,
    c09 TEXT,
    c10 TEXT,
    c11 TEXT
) ENGINE=InnoDB;

SHOW TABLE STATUS でRow_FormatがCompactになっていることを確認。

mysql&gt; SHOW TABLE STATUS LIKE 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-02-22 16:07:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

で、実際にデータを入れてみます。挿入するデータはこんな感じ。

INSERT INTO `t1` VALUES (
    'あいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえおあいうえお',
    'かきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこかきくけこ',
    /* 以下ご察し。。。 */
);

出力されたエラーはこんな感じ。

ERROR 1118 (42000) at line 1: Row size too large (&gt; 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.

「ROW_FORMAT=DYNAMIC」もしくは「ROW_FORMAT=COMPRESSED」にすれば助けてあげられるかも、だって。優しい。

っていうかそんな事はテーブル作成時に言ってくれ。

ということで「ROW_FORMAT=DYNAMIC」に指定しようと素直にALTER TABLEを実行するんですが、変わってくれません。

mysql&gt; ALTER TABLE `t1` ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 2

mysql&gt; SHOW TABLE STATUS LIKE 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-02-22 16:07:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment:
1 row in set (0.00 sec)

Create_optionsには見事にrow_format=DYNAMICってなってるんですがね。。。「ROW_FORMAT=DYNAMIC」を利用するにはInnoDB自体のファイルフォーマットを”Barracuda”に設定する必要があります。

ファイルフォーマットBarracudaを設定

ファイルフォーマットを”Barracuda”にするにはmy.cnfの設定を変更し、MySQLを再起動します。MySQL5.1の場合はInnoDB Pluginを有効にしないとだめなようで。

innodb_file_per_table
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda

設定が反映されれば以下の様になります。

mysql&gt; SHOW GLOBAL VARIABLES LIKE '%innodb_file_%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
4 rows in set (0.00 sec)
※ 2回再起動しないと反映されなかった。

これで晴れて「ROW_FORMAT=DYNAMIC」が利用できるようになります。

mysql&gt; ALTER TABLE `t1` ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql&gt; SHOW TABLE STATUS LIKE 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-02-22 16:45:36
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment:
1 row in set (0.00 sec)

イザ!検証!!

先ほどの(ご察し)SQLを再度投げてみます。

Query OK, 1 row affected (0.00 sec)

入った!やった!嬉しい!!

ということでInnoDBの8KBの壁はなんとか越えることが出来ました。ちなみに「ROW_FORMAT=COMPRESSED」はデータ圧縮するためのフォーマットで、容量が節約出来るとのことです。

これならデフォルトでROW_FORMAT=DYNAMICにしといてくれよって思うんですが、どうなんでしょか。

うん。8KB ÷ 20byte = 400カラム ならしばらく持ちそうだな。

コメントを残す