MyISAMとInnoDBのどちらを使うべきか

Twitterで話題になってたので簡単にまとめました。

●MyISAMにしか無い機能を使いたい場合はMyISAMを使うしかない
・全文検索 (TritonnやSphinx)
・GIS

●InnoDBの利点(MyISAMの欠点)
▲障害対応系
・クラッシュしても再起動するだけでリカバリができる
・クラッシュリカバリにかかる時間はテーブルサイズに比例するようなことはなく、コミット済みのデータは修復できる (巨大なMyISAMテーブルのREPAIRには数日単位で時間がかかることがある)
・オンラインバックアップができる
・INSERTやLOAD DATAなどを実行している途中でCtrl+Cでその更新系SQL文を止めても、テーブルは壊れないし、中途半端な状態で更新されることも無いし、スレーブが止まることも無い

▲性能系
・行レベルロックなので並列性が高い(MyISAMはテーブルロック)。またSELECTと更新系SQL文が競合しない。
・主キー検索が高速 (クラスタ索引のため)
・ダイレクトI/O(innodb_flush_method=O_DIRECT)を使えるためキャッシュ効率が高い
・インデックスの追加/削除をするにあたってテーブルを再編成する必要がなく、そのインデックスだけを再構築するので効率が良い(InnoDB Plugin)
・Insert Bufferという仕組みにより、セカンダリインデックスへのINSERT処理の効率がMyISAMよりも良い

▲従来は欠点だったが、InnoDB Pluginによって改善されたもの
・グループコミットが無効化されるため同時更新性能が著しく低下していた
・I/Oスレッドが事実上読み書き1本ずつしか無かったため並列性が低く、RAIDやSSDを有効活用できなかった
・CPUスケーラビリティが悪く、4CPUコアくらいまでしかスケールしなかった
・同じ量のデータを投入してもテーブルサイズがMyISAMよりも倍以上大きくなることがある(InnoDB Pluginの圧縮機能を使うことで緩和する手がある)

▲ほか
・InnoDBでは外部キーが使える


●MyISAMの利点
・WHERE条件無しのSELECT COUNT(*)が一瞬で返る
(InnoDBの場合はテーブルをなめる必要がある)
・メモリにおさまらないほど巨大なテーブルのフルテーブルスキャン系の処理効率が良い
(InnoDBではこうしたバッチ処理でバッファプールの中身が追い出されてしまうし、バッファプールの管理オーバーヘッドもあるが、MyISAMは専用のバッファプールを持たないので効率が良い)
・OSコピーによってテーブルの移動が極めて簡単にできる
・MERGEテーブルを使うことができる (InnoDBでも5.1のレンジパーティショニングを使えば十分なことは多い)
・リードオンリーのテーブルであれば圧縮できる
・ALTER TABLE ENABLE/DISABLE KEYSを使える。例えばインデックスなしの状態で高速にロードして、後からインデックスを有効化とかができる (InnoDB Pluginではインデックス単位の再構築ができるのでかなり緩和できる)
・InnoDBはテーブルのオープン処理がシリアライズされるため、大量の数のテーブルを初回オープンするような処理がきつい

▲ソリューションによって緩和できるもの
・クラッシュ時に壊れる問題やリカバリ(REPAIR TABLE)の遅さは、生きているスレーブを使って復旧すれば解決できる
・テーブルが巨大になることで引き起こされる性能問題は、小さなテーブルに分割することで解決できる


　自分は、特別な事情が無い限り、5.1最新版に含まれるInnoDB Pluginを勧めています(*注)。ログ蓄積系のテーブルではMyISAMが良いと考えている方が結構多いのですが、MyISAMでは複数のクライアントから同じテーブルに対してINSERTをすれば競合してしまいますし、InnoDBのInsert BufferのようなI/O最適化の仕組みが無い(詳しくは「Linux-DBシステム構築/運用入門」の9章あたりを参考にしてください)ので、InnoDBに比べても処理効率が悪いです。MyISAMを活用する場合は、上に挙げたようなテクニックを使って問題を緩和するのが効果的です。
　ちなみに、マスターをInnoDBにして、スレーブをMyISAMにするのは以前Postしたように特別な注意が必要です。

(*注追加) InnoDB Pluginの現時点での位置づけはベータです。ただし、Dynamic/Compressedという特別なテーブルフォーマットを使わない限り、既存のInnoDBテーブルと互換性があるので、InnoDB Pluginを使いつつ、不安定な現象に遭遇したらパラメータを変えて通常のInnoDBに戻すことが可能です。追加インストールやインストールし直し等が必要ないという手軽さが魅力です。

Okyuu.comからインタビューを受けました

　先日カカクコムさんが運営しているサイト「Okyuu.com」からインタビューを受け、その記事が公開されました。
　私は幼少の頃から技術者魂全開、というキャラとは程遠く、気づいたらこの業界でDB技術系の仕事をしていた、という程度なのですが、趣味と仕事が完全に一致したという恵まれた方はむしろ少数派で、多くの方は生活のために(それと多少の興味と一致して)この業界で仕事をしているのだと思います。この業界はコードを書くのが趣味でなければ生きていけない、というプレッシャーをひしひしと感じさせますが、そうでない人にとっても活躍の場はあるのだ、という安心感を持っていただけると幸いです。
　インタビュー記事について、少しだけ補足をしたいと思います。

・お金を払うユーザーを大切にするのは当然ですが、無償で使うユーザーを無視しているわけではありません。そもそも無償で使うユーザーを無視するようではOSSビジネスは成立しません。多くの利用者が試し、時に地雷を踏みそれを修復するサイクルを繰り返すことで、ようやくお金を払っても良いかなという品質に達すると考えています。誰も使っていないプロダクト(地雷を踏むリスクが高い)にお金を払うユーザーは、昨今では非常に少ないのではないでしょうか。

・第3者企業がOSS製品をさんざん利用した挙句、ちょっと機能を追加してクローズドソースにして「自社製品です」と言い張って販売するのは、私は「邪道」のビジネスだと考えています。もちろん程度問題で、ユーティリティライブラリ程度なら全然いいと思いますが、コアの機能がもろにOSSを使っているのなら、それを拡張してクローズドソースにして販売するくらいなら本家にフィードバックしろよ、と思うわけです。

・OSSに貢献する道は、パッチを書くだけではありません。たとえば新バージョンのバグを見つけてバグレポート上で再現手順を報告するというのも立派な貢献です。こうしたバグ報告によって製品の品質は徐々に上がっていきます。その意味では、会社としてはお金を出せないしパッチを書くほどのスキルは無いけどOSSに貢献したい、というような方は、できるだけ新しいバージョンを積極的に使って、バグを見つけて報告するのが良いのではないかと思います。ただし、セキュリティ上の脆弱性に関しては、バグフィックスよりも先にBlog等で発信して一般に認知されたりすると、攻撃の対象になりかねないので気をつけて頂きたいと思います(MySQLのバグレポートでは、脆弱性の問題については、報告を受けた後に一般ユーザーが閲覧できないように権限設定されます)。

スワップサイズをゼロにしてはいけない

　先月発売された書籍「Linux-DBシステム構築/運用入門」は、なかなか上々の売れ行きとなっているようです。Amazonではしばらく「1-2ヶ月待ち」の状態が続いてしまっていたのですが、最近になってようやく解消され、容易に入手できるようになっているようです。Amazonの在庫切れ問題がひと段落したところで、これからは書籍のサポート的な情報を書いていくことにします。
　まず、本書を購入された皆さまありがとうございました。結構な数の方がBlogやTwitter等で、この本をほめてくださっていることに大変感謝しています。まだ本自体の認知度が低い(存在自体を知らない顧客も多い)ので、普及活動をしつつ、これからも読者の期待に応えられる記事を書いていきたいと思っています。

　最初は、よく見かけることの多い「メモリ管理」の話題を取り上げようと思います。第12章では、メモリ管理とスワップ領域に関する解説をしています。64ビット機と数十GBクラスの大容量メモリを搭載するのが現在のトレンドになりました。ディスクI/Oの速度はメモリアクセスに比べて極端に落ちますから(第10章参照)、できるだけ多くのデータをメモリ上に置いて高速化をはかるというのがDBチューニングの定石です。多くの場合、メモリを増設することで参照性能だけでなくINSERTなどの更新性能も上がるというのは第9章などで触れている通りです。またダイレクトI/Oを使ってキャッシュ効率を上げることも効果的です。InnoDBならinnodb_flush_method=O_DIRECTを指定すれば良いです。

　メモリ管理の設定でよく見るのが、スワップサイズをゼロにしているケースです。スワップサイズをゼロにすれば、スワップが発生しませんが、これは問題があります。本章で書いているように、プロセスが実メモリを使い切ってしまった場合に、スワップできないのでOOM Killerによって殺されてしまいます。また、このときにハングアップ状態がしばらく続き、挙句の果てに異常終了してしまうためです。単に再起動したとしても、ダイレクトI/Oであればプロセス空間のキャッシュだけでなく、ファイルシステムキャッシュ上に何も載っていない状態からスタートするため、ディスクI/Oが多発して性能が一気に低下します。もちろんDRBD等によるアクティブ/スタンバイ構成でフェイルオーバーするような場合も、フェイルオーバー先にはキャッシュに何も乗っていないため、同じように性能が落ちます。スワップサイズが小さい場合も、それを使い切れば同様にOOM Killerに殺されてしまいます。このため、「スワップサイズをきちんと(物理メモリの半分くらい)取り、ダイレクトI/Oを使い、プロセスよりもファイルシステムキャッシュが優先的にスワップされるようにvm.swappinessをゼロにする」という方法を紹介したというわけです。もちろん、スワップが発生するというのはディスクI/Oが頻発するという意味なので、それを避けるように実メモリの範囲内におさまるようにチューニングするのが大切です。

　なお、MyISAMテーブルなど、ダイレクトI/Oをサポートしていないストレージエンジンでは、RDBMSのプロセスサイズが小さくなり、ファイルシステムキャッシュのサイズが大きくなる傾向にあります。この場合、プロセスだけでメモリ空間を使い切る(OOM Killerによって殺される)ことはまず無いでしょう。