Hatena::ブログ(Diary)

naoyaのはてなダイアリー

July 29, 2006

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 にしてるよー」なんて人がいたら使用感とかを教えてくれたりすると嬉しかったりします。

hyoshiokhyoshiok 2006/07/30 21:37 oprofileを取り合えずとってみると、MyISAMがどこで時間を食っているかがわかるかと思います。そこからはじめますかね…
↑何がなんでもoprofileというのは、バカの一つ覚えなんですが。

nana 2006/07/31 13:40 トランザクションを実現するための犠牲って思いのほか多いんですよね。
InnoDBではMVCCによる並列処理制御を行っていますが、そのオーバーヘッド
というのはとても大きいです。全てのレコードにシステム時間(データベース
バージョン)が定義されていて、様々な客観的データからではその時点での
データの整合性を図ることはできず、必ず実レコードを確認しなければなり
ません。よって、参照系ではMyISAMに軍配が上がります。簡単なベンチマーク
では差が現れにくいですが、実際使用されるような複雑な環境においては
顕著に差がでるとおもいます。
あと、InnoDBには
・データサイズでレコード数を知ることが出来ないので、MyISAMに比べて
countが圧倒的に遅い
・InnoDBにおけるupdateはレコードを追加するのと等しい。よって、
updateが多数発行される場合はデータ量の増加が無視できない。
再利用されるタイミングは不明で、またdelete時点では消えない。
という性能と運用面に大きく影響がある欠点がありますので、この点も
注意が必要です。

素人ですが素人ですが 2006/07/31 17:10 本家サイトのマニュアルにもかかれてますが、myISAMのロックの問題は、インデックス使わないような遅いselectが混在する場合だけじゃないでしょうかね。
インデックス使って、1レコードだけをselectしたりupdateしたりするのは、perl-dbi経由で手元のwinマシンで1/2000sec程度で行えます。つまり、すべてのクエリで適切なインデックスを使って十分高速に処理できれば、webアプリでロック待ちが問題になることはほとんど無いと思います。
遅いselectは、テーブル分割したり、検索用のテーブル作ったりで大体対処できるかなぁと。
レプリケーションは仕組みよく知らないのですが、スレーブ更新中に同じ問題が起きないんですかね?
まあ、ハードに金かけられるなら、innoDBのほうがリニアにスケールできそうだから楽でしょうね。アプリ側であれこれ考えなくていいですし。
ちなみに、自分で単純なベンチやってみたら、select,updateは、ほとんど変わらずでしたが、insertだけは、なぜかinnoDBが60倍ぐらい遅かったです。。
結構、webで拾えるベンチマークの結果は、どこもバラバラでどれを信用したらいいのか悩みますね。
http://www.drk7.jp/MT/archives/000941.html
google上位のここだと、10倍以上の差があるけど(哂)

tttt 2006/08/01 00:52 ユーザーからselectのみのtableはMyISAM。
ユーザーからupdateが一つでも入る物はinnoDBにしています。

MySQLのupdateは単純にselectされた行をdeleteしinsertしている
だけのような気がしますが違いますかね。

紹介されている「実践ハイパフォーマンスMySQL」が全く役に立たなかった
記憶があります。

たにたに 2006/08/24 03:40 こんにちは。有名どころのサイトですと
FlickrもInnoDB利用しているようですね。

基本的にDBは2台以上の構成にして
MasterだけInnoDB、Slave以下はMyISAM
で運用していくのが効率が良いかなぁ、と思いますが
台数増えると故障率も上がりますし、何ともですね。