Y-Ken Studio

新しもの好きのデータエンジニアが四方山話をお届けします。

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


photo by byte

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に詳しくまとめていましたので、参照先のみの紹介とします。

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

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

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%を割り当てるのが良い"と記述されています。

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

  • /etc/my.cnf
    • key_buffer_size=2000M
    • innodb_buffer_pool_size=8M
  • MyISAMからInnoDB変換後のテーブルが4GBほどで、日々少しずつ増えている
  • マシンのメモリが8GB

この状況であれば、my.cnfの設定は次のようにチューニングすると良いでしょう。

  • key_buffer_size=8M (mysqlの管理データベース用)
  • innodb_buffer_pool_size=6000M (OS側に500MB、残り約7.5GBの8割をInnoDBに割り当て)

まとめ

MyISAMからInnoDBへの移行は、アプリケーションの改修が欠かせません。
なかなか骨の折れる仕事ですが、将来きっと使えるようになるGTID+mysqlfailoverなどの魅力的な運用ツールの導入という選択肢を増やす意味でも、この機に移行しておくとハッピーな未来が待っているかもしれませんね。

そして、2013年11月には改訂版「実践ハイパフォーマンスMySQL 第3版」が刊行されておりますので、まだチェックされていない方はこの年末に読んでみてはいかがでしょうか。

実践ハイパフォーマンスMySQL 第3版

実践ハイパフォーマンスMySQL 第3版


このエントリは「MySQL Casual Advent Calendar 2013」の5日目でした。
明日は @yoku0825 さんです!

http://www.zusaar.com/event/1847003

広告を非表示にする