運用視点なMyISAMとInnoDBと。

MySQL5.5からトランザクション処理ができるInnoDBがデフォルトストレージとなりましたし、とりあえずInnoDBにしとこうという風潮から、ストレージがInnoDBであることも多いのですが、実は蓋を開けて見るとまだまだMyISAMで動いているサービスがたくさんあります。今回は運用面から見た両者の違いをみてみたいと思います。

同じMySQLですが、InnoDBの運用とMyISAMの運用は注意するポイントが違います。

ロック方式

一番大きい違いはロック方式の違いでしょうか。InnoDBは行ロック方式(*1)、MyISAMはテーブルロック方式です。データをINSERTやUPDATEする時はセマフォ制御のためロックされますが、その時の挙動が違います。

たとえばUPDATEのクエリを投げると、MyISAMの場合は対象テーブル全体がロックされ、その後のクエリが”詰まり”ます。なので重いクエリを発行するとその分処理待ちが発生します。

テーブルロックにも2つ方式があります。

共有ロック
(READロック)
他のプロセスは読み込みはできるが、書き込みができなくなる
排他ロック
(WRITEロック)
他のプロセスは読み込みも書き込みもできない。

InnoDBの感覚で気軽に参照クエリや更新クエリを発行すると、MyISAMではそのテーブルが排他ロックされ、そのテーブルに関連するプログラムが動かなくなってしまう、などの障害が起きてしまいます。

(*1) クエリによってはテーブルロックが発生します。

チューニング

チューニングも大幅に違います。InnoDBでは、innodb_buffer_pool_sizeのパラメータが一番大きなポイントです。InnoDBはMySQLがテーブルデータのキャッシュを管理しており、その最大サイズを指定できます。なるべくinnodb_buffer_pool_sizeを大きくします。
MyISAMでは、MySQLがテーブルデータのキャッシュを管理しません。テーブルデータに関してはOSにまかせています。インデックスはMySQLが管理していますので、key_buffer_sizeでインデックスキャッシュのサイズを指定します。

InnoDBの場合はbuffer_pool_sizeを使用可能なメモリ80%、
MyISAMの場合はkey_buffer_sizeを使用可能なメモリの25%が目安です。

設定項目は数多くありますが、まずはこの部分に注目すると手っ取り早くパフォーマンスを上げることができます。

性能

MyISAMはシンプル故に高速です。ですが、MySQLのバージョンが上がってきてその差は少なくなってきています。差が出てくるとすれば、更新系クエリのトランザクションによるオーバーヘッドでしょうか。

バックアップのとりやすさ

MyISAMは一つのテーブルに対し、frmファイル、MYDファイル、MYIファイルの3つのバイナリデータを作成します。これをコピーするだけでバックアップできます。InnoDBの場合はfrmファイルのみが作成され、レコードとインデックスデータはテーブルスペースに保存されます。テーブルスペースはibdata1という名前で保存されています。一つのテーブルに対してテーブルスペースができるわけではありませんので(設定次第ですが)、テーブルが多いとバックアップが難儀な傾向です。
※mysqldumpを使ったり、MySQL Enterprise Backupを使う方法もあります。

壊れやすさ

MyISAMは壊れやすく、InnoDBは壊れにくいです。実際MyISAMは運用中に何度か壊れてしまったことがあります。その時は運が良ければREPAIRできます。できない場合もありますが。
MyISAMの修復

全文検索

MyISAMはFULLTEXTインデックスという仕組みがあり、全文検索に対応しています。サービス立ち上げ当時は気にならなくとも、データ量が増えていくにつれ、全文検索が遅くなります。こういうときMyISAMだとFULLTEXTインデックスを使えばいい、ということですね。これが理由でMyISAMを選択する場合もあると思います。しかしInnoDBもMySQL5.6から全文検索ができるようになりました。それ以前のMySQL InnoDBの場合は、全文検索エンジンを別途用意したり、なんらかのキャッシュ機構を用意して対応します。

運用あるある

共有ロックなのに排他ロックになる

参照クエリと更新クエリでは更新クエリの方が優先順位が高いです。重い参照クエリを投げてそのテーブルがロックされたあと、更新クエリを発行すると、実行中の参照クエリの次に更新クエリを実行しようと優先順位を上げます。その瞬間、共有ロックだったはずのテーブルが排他ロックになります(更新クエリが終わってから次の参照クエリを実行しようとするため)。更新処理が終わるまでは、参照できなくなってしまいます。

MySQLのバージョンアップ

基本的にバージョンアップによって処理速度があがりますし、脆弱性やバグが解消されますので、アップデートはするべきですが、当初からバージョンアップを見据えていないと相当難しい対応になります。ここらへんはOracleですと結構後方互換が残されてるはずですが、MySQLの場合今まで使っていたクエリが使えなく、、ということに直面します。(マイナーアップデートだとそこまで難儀ではないかもしれません)

テーブルのフラグメンテーション

一気に大量のデータを削除すると、MyISAMの場合はフラグメンテーションが起こり、対象テーブルの性能が落ちます。少しするとインデックスや統計の更新で最適化されますが、直後はもっさり、なんてこともあります。その場合はOPTIMIZEかREPAIRをかけると修復されますが、REPAIRはオフライン(サービス断)しなければいけない難点もあり頭を悩ませます。

レプリケーション遅延

MySQL5.6ではマルチスレッドスレーブが実装されました。逆にいえば、それ以前のレプリケーションのための処理プロセスはひとつ(*2)だったので、大量の更新クエリを発行されると、その分遅延が起こります。このプログラムはマスタ、このプログラムはスレーブを参照、という風にきっちり設計しないと特に課金系処理では痛い目を見ます。
(*2) 正確にはI/OスレッドとSQL処理スレッドがあります

HUPシグナルが送れない

MySQLモニタやターミナルを閉じた時に、HUPシグナルが送られますが、MySQL4.0系ではHUPシグナルによるプロセスの終了が行われません。つまり、重いクエリを投げる→Ctrl+Cで切断→実はプロセスが未だ生きていて…ということに陥りがちなので、しっかりshow processlistでプロセス一覧を確認するべきですね。

ALTER TABLEが終わらない

待ちましょう。。というかデータ量がおおい場合は、しっかり計画的にALTER TABLEしないとメンテナンス時間が酷いことになりますので検証をするべきです。DUMPとってインポートの方が早い場合があります。

いまどっちを選ぶか

保守、運用では直接DBにデータを流したり、参照したりすることは多いですが、その場合でもInnoDBの方が今どきは有利でしょう。下手するとテーブルロックで他に影響をおよぼす可能性があるので、MyISAMの方がより気を使います。ただし、バックアップの取得やリストアなどはMyISAMの方が楽ですので、トランザクションがいらないサイトであれば割りきってMyISAMを使うのもありかなと思います。

MySQLも長い歴史がありますし、ここでぜんぶは書ききれませんね。。。今回はとりあえず思いついたことを記載していますが、公式ドキュメントをみると既知の問題があったりと、読むだけでも結構面白かったりするのでよんでみてください。今後ほかのストレージエンジンや、MySQLの設計、運用についてもうちょっと細かく書いていきます。