MyISAM vs InnoDB

あくまで憶測で仮説でしかないんですが。

MySQL のストレージエンジンのうち代表的な二つ、MyISAM と InnoDB はよく

• MyISAM: Read は速いけどテーブルロックのため並行性が低い。運用が簡単。
• InnoDB: MyISAM より Read は遅いけど並行性が高い 。行レベルロックなので。あとトランザクションや外部キー制約。運用が MyISAM よりちょっとめんどくさい。

という区別がされます。ここから転じて、

• MyISAM は参照系クエリが大部分を占める場合に適用すると良い。例えば blog アプリケーションとか。
• InnoDB は更新系クエリが多い場合に適用すると良い。

と言わたりします。実践ハイパフォーマンスMySQL でも第2章 ストレージエンジン(テーブル型) P.30 に

アプリケーションでトランザクションを使用する必要がなく、主に SELECT または INSERT と UPDATE クエリを実行するのであれば MyISAM がふさわしい。多くの Web アプリケーションは、このカテゴリに入る。

と、ざっくりとしたまとめがなされています。僕もこれをその通りに受け取ってたんですが。

以前から疑問に思ってるのがこの MyISAM の「速い」というのがどう速いのかというところ。普通に考えたら並行性を確保するためのオーバーヘッドが少ないなどの理由でマシンリソース的に優しい、おおざっぱにいうと CPU をそんなに使わないってことなのかなあと思います、多分。正確なところは誰か詳しい人がすごい勢いで解説してくれるはず!

一方、はてなぐらいの規模で DB とかを使ってるとよくわかるんですが、単位マシンあたりの CPU を使い切ってしまってリソースが不足することってのは (DB に限らず) 結構希です。CPU リソースを使い切る前に、多くの場合はメモリが足りなくなったりディスク I/O に全体が引きずられてしまったりする。特にディスク I/O は大敵ですね。このためにみんな必死にキャッシュしたりしてるわけで。

このとき MyISAM を使ってる場合にディスクI/O 以外に問題になるのが、例によってテーブルロックです。MyISAM は読み出し時に共有ロック、書き込み時に排他ロックをそれぞれテーブル単位で獲得するわけですが、はてなのサービスのように不特定多数から同時にアクセスがあるサービスのバックエンドの DB では、テーブルロックで広範囲が Lock されまくるせいでデータベースからのデータの読み出しにえらく時間がかかってしまったり、最悪デッドロックみたいになって MySQL がハングアップするという問題が発生しがちです。サーバーが悲鳴を上げたのでなんだなんだと言って show processlist すると Lock りまくり、みたいな。

この Lock りまくり状態が、おそらく MySQL なデータベースにおいて「サーバーリソースはまだ結構あるのにそれらを生かし切ることができない」という問題の主要因のひとつです。

この辺考えると、マシンも安いし簡単に DB を増設できる今の時代、ある一定規模以上のトラフィックのあるサイトでは MyISAM で CPU に優しいシステムを選択するよりかは、マシンリソースを消費してでも並行性の高い InnoDB を選択するほうが、総体でのパフォーマンスは良かったりするんじゃないかなあという疑問がふつふつと沸いてきます。つまり、これまでのように「参照クエリが多いときは MyISAM」という定番の方針を疑って、大きなサイトでは「参照クエリが多いアプリケーションでもとりあえず InnoDB」 っていう方針になり得るんじゃないかなあと。資源を有効に活用できるうえ、耐障害性も高まるという意味で。

よく全体の 80 % 〜 90 % のクエリが参照の場合は MyISAM と言われますが、MyISAM の Write 時の排他ロックによる危険性は更新の割合よりも、更新の回数によって高くなるんだと思います。トラフィックが大きいサイトではどんなに参照系クエリが割合として多かったとしてもある一定数以上の更新が発生するようになったところで、リソースはあるけど Lock しまくり問題が顕在化し始めるんじゃないかという仮説。

Mixi がほとんど InnoDB使ってる ってのもこの辺が理由じゃないかなと思って。 Q & A に "実際には一台一台の参照頻度がそんなに高いわけでもないし、ロックさせるのにテーブルロックとかしたくないからさ" とありますが、詳しく言うととこういうことでしょうか? なんか海外のプレゼンの資料とかを見てても「InnoDB いいぜ!」みたいなスライドを見かけたりします。明確な理由はそんなに書いてないことが多いんですが、つまりはそういうことなのかな。

ベンチマークも取ってないし実際 MyISAM で運用してるでかいアプリケーションを InnoDB にして試したわけでもないので、InnoDB にしたらしたでそんなにうまくはいかないのかもしれませんが。運用がめんどくさいのは萎えますしね。いまのところ弊社では更新処理がやたらに多いのを InnoDB にしたら調子が良くなったというのはありますが、参照多めで昔から MyISAM 使ってるのを InnoDB に変えた、というケースはありません。

ということで、でかいサイトで「参照系多くてトランザクション使ってないけど InnoDB にしてるよー」なんて人がいたら使用感とかを教えてくれたりすると嬉しかったりします。

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