Engineering Design Startup Education Event

MySQLのファイルフォーマットをAntelopeからBarracudaに変更する

2018.09.14

InnoDBとは

MySQLのデータベースエンジンの一つ。他のデータベースエンジンにはMyISAMがある。MyISAMのロックがテーブル単位なのに対して、InnoDBのロックはレコード単位。レコードの操作を行う際にテーブル全体をロックするとテーブルの他のレコードも変更できなくなるが、行単位のロックであれば同時に複数のレコードを変更できるため、効率が良い。他にもトランザクションにも違いがあるようなので、別途まとめる。

InnoDBのファイルフォーマット

AntelopeとBarracudaがある。デフォルトはAntelopeだが、推奨はBarracudaとなっている。

何も考えずにデータベースを作成するとファイルフォーマットはAntelopeになってしまう。4バイト文字をデータベースに保存しようとしてエンコーディングをutf8mb4にすると、Index column size too large. The maximum column size is 767 bytesというエラーが出てしまう。これを解消するためにはファイルフォーマットをBarracudaに変更する必要があり、既存のテーブルのファイルフォーマットの変更作業も必要になる。

可能な場合、新しいテーブルには Barracuda 形式を使用することをお勧めしますが、MySQL 5.5 では、異なる MySQL リリースを含むレプリケーション構成との最大限の互換性のために、デフォルトのファイル形式は引き続き Antelope です。 MySQL 5.6 リファレンスマニュアル 14.8.1 ファイル形式の有効化

ファイルフォーマットの確認方法

SHOW TABLE STATUSコマンドで確認する。

-- テスト用データベースを作成
mysql> CREATE DATABASE file_format1;
Query OK, 1 row affected (0.00 sec)

-- 作成したデータベースを選択
mysql> USE file_format1;
Database changed

-- テスト用テーブルを作成
mysql> CREATE TABLE `table1` (id int NOT NULL AUTO_INCREMENT, content varchar(255) NOT NULL, PRIMARY KEY (id));

-- テーブルのステータスを確認
mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: table1
         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: 1
    Create_time: 2018-09-14 23:41:58
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Row_format: Compactの部分で判別ができる。

InnoDBファイルフォーマット	ROW_FORMAT
Antelope	Compact または Redundant
Barracuda	Compressed または Dynamic

先ほどのtable1はRow_format: CompactなのでファイルフォーマットはAntelopeだとわかる。

新しくデータベースを作るときにファイルフォーマットをBarracudaにする

新しくデータベースを作成する場合は、my.cnfに設定を書いておくだけでファイルフォーマットを変更することができる。

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

character-set-server=utf8

innodb_file_per_table = 1           # ← この行と
innodb_file_format = Barracuda      # ← この行と
innodb_file_format_max = Barracuda  # ← この行を追加

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
default-character-set=utf8

MySQLサーバーの設定を変更するので、サーバーを再起動する。

$ sudo systemctl restart mysqld

ファイルフォーマットが変わったか、実際にデータベースとテーブルを作成して先ほどと同じように検証してみる。

-- テスト用データベースを作成
mysql> CREATE DATABASE file_format2;
Query OK, 1 row affected (0.00 sec)

-- 作成したデータベースを選択
mysql> USE file_format2;
Database changed

-- テスト用テーブルを作成
mysql> CREATE TABLE `table2` (id int NOT NULL AUTO_INCREMENT, content varchar(255) NOT NULL, PRIMARY KEY (id)) ROW_FORMAT=DYNAMIC;

-- テーブルのステータスを確認
mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: table2
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic  -- ← Dynamicになっている
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2018-09-15 00:16:06
    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)

ファイルフォーマットをBarracudaでテーブルを作成することができた。

既存のデータベースのファイルフォーマットをBarracudaに変更する

先ほどと同じようにmy.cnfを編集した上で、ALTER TABLEコマンドでテーブルを変更すれば良い。先ほど作成したデータベースfile_format1のテーブルtable1のファイルフォーマットをBarracudaに変更してみる。

mysql> USE file_format1;
Database changed

mysql> ALTER TABLE table1 ROW_FORMAT = DYNAMIC;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: table1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic -- ← Dynamicに変更できている
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2018-09-15 00:20: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)

MySQL RDBMS インフラデータベース InnoDB