Loading
BLOG 開発者ブログ

2020年12月23日

エラーから学ぶMySQL InnoDBのレコード長制限


MySQL(InnoDB)にはデータ型やカラム数など様々な制限がありますが、レコード長にも制限があることはご存知でしょうか?
データベースに新しいテーブルやカラムを追加するとき、データの上限や使い方を考えてデータ型やインデックスを設計していると思いますが、レコード長を意識して設計している方は意外と少ないかもしれません。
今回、MySQL5.6環境で発生したエラーからレコード長の制限について学ぶことができたのでご紹介したいと思います。

この記事はアイソルート Advent Calendar 23日目の記事です。
こんにちは。
クラウドソリューショングループのakahane.tです。

昨日はnamiki.tさんの「【ノーコード】Amazon Honeycode で Todo リストを作ってみた」でした。

アジェンダ

  1. はじめに
  2. きっかけ
  3. 調査
  4. 対策
  5. まとめ
  6. おわりに

はじめに

今回はMySQL5.6環境でデフォルトの設定を前提としています。
また、起点となったエラーはMySQL5.7以降の環境では設定のデフォルト値が対策後の値になっているため、設定が変更されていなければ発生しません。
しかし、レコード長の制限は5.7以降も残っていることや、既存のサービスの運用やクラウド移行など触れる機会は少なからずありますので最後までお付き合いいただけると幸いです。

きっかけ

入力フォームのデータの保存用のテーブルを作成していた際に、複数のテキストフィールドに長文を入力した場合だけ発生する下記のエラーがありました。

ERROR 1118 (42000): 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.

LONGTEXT型のような数GBのデータでも格納できるのにも関わらず、TEXT型で「too large」とエラーになることが不思議で原因についても調査してみました。

調査

なぜ1レコード8KBまでなのか

公式ドキュメント(14.12.2)によると、1レコードの長さはページサイズの約半分に制限されているそうです。

The maximum row length is slightly less than half a database page. For example, the maximum row length is slightly less than 8KB for the default 16KB InnoDB page size, which is defined by the innodb_page_size configuration option.

今回はデフォルト設定の環境ですので、ページサイズが16KBと設定されていたため1レコードの長さが8KBと制限されていました。

なぜ8KB以上のTEXT型が格納できるのか

公式ドキュメント(14.11)によると、TEXT型のような可変長型のカラムは先頭768Bだけレコードにインラインで格納して残りは外部のページに格納されるそうです。

Tables that use the COMPACT row format store the first 768 bytes of variable-length column values (VARCHAR, VARBINARY, and BLOB and TEXT types) in the index record within the B-tree node, with the remainder stored on overflow pages.

サイズの大きなTEXT型カラムは1カラムあたり768B必要なことになりますので、1レコード8KBの制限下では10カラムまでしか格納できないことになります。
今回私が冒頭のエラーに遭遇したときは15項目ありましたので、この制限に引っかかっていたことになります。

対策

原因が分かったところで、対策方法も調べてみるときちんと設定が用意されていました。
ここからはサイズの大きな可変長型データを11カラム以上格納するための手順になります。

  1. innodb_file_formatをBarracudaに設定する。(MySQL5.7以降はデフォルトでBarracuda)
    innodb_file_format = Barracuda
  2. innodb_file_per_tableを1(ON)に設定する。(MySQL5.6.6以降はデフォルトで1)
    innodb_file_per_table = 1
  3. テーブル作成または編集でROW_FORMATをDYNAMIC(またはCOMPRESSED)に設定する。(MySQL5.7以降はデフォルトでDYNAMIC)
    CREATE TABLE tbl_name (
        ...
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
    ALTER TABLE tbl_name ROW_FORMAT=DYNAMIC;

まとめ

  • 1レコードのサイズはデフォルトで8KBに制限されている。
  • MySQL5.6のデフォルト設定ではサイズの大きな可変長型データは10カラムまで。
  • MySQL5.6でサイズの大きな可変長型データを11カラム以上格納する場合は次の設定を行う。
    • DB設定
      • innodb_file_formatをBarracudaに設定する。
      • innodb_file_per_tableを1(ON)に設定する。
    • テーブル設定
      • ROW_FORMATをDYNAMIC(またはCOMPRESSED)に設定する。
  • MySQL5.7以降はデフォルトの設定でもサイズの大きな可変長型データを11カラム以上格納できる。

おわりに

今回は見慣れないエラーと遭遇できたことでMySQLの理解をより一層深めることができました。
DBは自由度が高いイメージを持っていたので、レコード長にも制限があることは私にとっては意外なことでした。

今回の起点となったエラーは古い環境でしか発生しないものですが、アップデートできる人がいない環境やリスクとメリットを鑑みてアップデートしていないという環境もあると思います。
そういった環境では今も関係のある話であり現時点で最新のMySQL8でも無関係ではない制限でしたので、今回の記事がみなさんのお役に立つ日が来れば幸いです。

明日の記事はkadekari.nさんによる「簡単操作!新機能 Motion Editor でアニメーション作成」です、お楽しみに!

akahane.tのブログ