MySQLでMyISAMからInnoDBに乗り換える際に知らないとハマる、怖い話

MySQLといえば、巷ではInnoDBばかり注目され、MyISAMの地下アイドル化がにわかに語られる今日この頃、皆様いかがお過ごしでしょうか。

まあカジュアルにストレージエンジンを変換するだけで済むなら、簡単なのです。

-- legacy_my_tableをInnoDBストレージエンジンに変換する
ALTER TABLE legacy_my_table ENGINE=InnoDB;

よし終わった！さあランチタイムだ！
・・・と片付けてしてしまうと、悲劇が起こるかもしれません。(>o<;)

それでは本日、MyISAMからInnoDBへ移行するなら知っておきたい意外な落とし穴とTipsを紹介します。

AUTO INCREMENTの挙動が違う落とし穴

以下に該当するクエリを利用している場合には、注意が必要です。私はハマりました。

INSERT IGNORE INTO ...
INSERT INTO ... ON DUPLICATE KEY UPDATE ...
LOAD DATA ... IGNORE INTO ...
DELETE FROM ... WHERE id = ...

具体例と共に書こうと思ったのですが、過去の自分がgihyo.jpに詳しくまとめていましたので、参照先のみの紹介とします。

ストレージエンジンをMyISAMからInnoDBへ切り替える際の注意
http://gihyo.jp/dev/clip/01/groonga/0006?page=3

PRIMARY KEYの2カラム目以降にAUTO_INCREMENTを仕込んである場合の落とし穴

1カラム目ごとに2カラム目をインクリメントさせるというスキーマにしている場合には対象となります。おそらくこれでは何を言っているのか分からないと思いますので、詳しくは次のブログ記事をご参照ください！

MySQLのInnoDBでもPRIMARY KEYの2カラム目以降にAUTO_INCREMENTを使いたい
http://tech.gmo-media.jp/post/62678005904/auto-increment-second-column-on-innodb

InnoDBで使えるようになったという全文検索機能の落とし穴

MySQL 5.6.4からInnoDBでもFull Text Searchが出来るようになったというお話もありますが、CJKはもといNGRAMにすら対応していない状態のため、日本語圏で使うとしたらスペース区切りのタグ検索程度にしか使えません。
それだけでなく、InnoDB FTSはMySQL Bugsに続々とレポートが上がってくるという開発段階のため、今は素直にMroongaの利用を検討しましょう。
Mroongaのラッパーモードを利用すれば、フルテキストインデックスに関してはMroonga、それ以外はInnoDBとを利用するという組み合わせもできます。

オンラインバックアップが出来るようになる

MyISAMを撤廃できた暁には、バックアップを取得するだけのためにレプリケーションスレーブサーバを用意しなくても良くなります。経費削減ですね。
これまでMyISAMテーブルのバックアップ時には、全てのテーブルをロックする必要がありました。しかしInnoDBへ移行することでそれは過去の話となり、オンラインバックアップが取れるようになります。
具体的には、mysqldumpコマンドであれば次のように無停止でのバックアップが取れるようになります。

$ mysqldump –opt –single-transaction –master-data=2 –hex-blob –flush-logs –order-by-primary mydatabase > mydatabase.sql

key_buffer_size と innodb_buffer_pool_size のチューニング

MyISAMからInnoDBへ乗り換え後には、InnoDBの性能を最大化するためにもパラメータのチューニングは欠かせません。

MyISAMのチューニングでは、key_buffer_sizeを必要以上に設定したことでLinux側のページキャッシュが不足し、かえって遅くなることもありました。それはインデックス領域はメモリに載せるが、データ領域はページキャッシュから呼び出していたためです。
しかし、innodb_buffer_pool_sizeはインデックス・データ共にページキャッシュは使わず独自のバッファ領域を利用するため、大いに割り当てましょう。MySQL公式ドキュメントには、"専用サーバの場合物理メモリの80%を割り当てるのが良い"と記述されています。

具体例と共に、これより説明します。