DBチューニングではディスクI/O性能を注視する

　DBチューニングにおいて、気を配るべきところは数多くありますが、中でも真っ先に見るべきところはディスクI/Oでしょう。なぜかというと、メモリアクセスに比べてHDDの方が圧倒的に遅く、最もパフォーマンス阻害要因になりやすいためです。ディスクI/Oネックの解決方法を探っていくと、「テーブル/インデックス設計やSQL文の見直し」に行き着くこともまた多いです。これらが不適切だと、結果として大量のレコードをアクセスすることになり、ディスクI/Oが多く発生してしまうためです。根本的な原因はディスクI/Oにあります(CPUネックになることもありますが、その例は別の機会に取り上げます)。
　ディスクI/Oには大きく分けてシーケンシャルアクセスとランダムアクセスの2種類のアクセスパターンがありますが、RDBMSではインデックスアクセスが主体となるため、ルート→ブランチ→リーフ→実レコードという経路でのランダムアクセスが発生します。
　秒間に処理できるI/O数のことをIOPSと呼びます。HDDへのランダムアクセスはシーク待ち時間や回転待ち時間のコストが極めて高く、1回あたり5ms級の時間を要すため、HDD1本あたりのランダムIOPSはせいぜい数百程度にしかなりません。メモリアクセスが数十ナノ秒単位ですから、HDDと比べて数十万倍クラスのアクセス性能差があります。1個のSQL文を処理するという観点からは、1回のメモリアクセス vs 1回のディスクアクセスにはならず、構文解析/実行計画生成/文字列コピーなどさまざまな処理が行なわれるので、格差という意味では数十万倍ほどにはなりません。それでも100倍くらいの差は簡単に発生してしまいます。すべてがインメモリの状態であれば、単純な主キー検索について、秒間数万クエリくらいは処理できます。一方で大半がディスクに向かう場合は、4本くらいのRAID1+0構成であっても毎秒数百クエリ程度にとどまってしまうことは少なくありません。
　仮に、インメモリで完結するSQL文の処理時間が1、ディスクアクセスを伴う場合の処理時間が100としましょう。バッファプールのヒット率が100%の場合は、100個のリクエストを処理するのにかかる延べ時間は1×100で100です。一方ヒット率95%の場合は、1×95+100×5=595となり、6倍くらいかかってしまいます。わずかなヒット率のダウンで、スループットが1/6にまで低下してしまう計算です。ヒット率が80%の場合は、1×80+100×20=2080で、最初の状態の約1/21まで落ちてしまいます。バッファプールにおさまるようにアクセスさせることが重要なことが分かります。実際のRDBMSのパフォーマンスも、これと似たような傾向を示します。データ量の増加に対してスループットがいかに低下していくかという例は、SH2さんという方が過去に行なったInnoDBのベンチマークが分かりやすいです。
グラフ：http://f.hatena.ne.jp/sh2/20090704095053
エントリ：http://d.hatena.ne.jp/sh2/20090705

　テーブルやインデックスのサイズに対して実メモリが十分に大きい場合、あるいはテーブル/インデックスサイズが大きくてもアクセス範囲が限定されているような場合は、すべての処理がキャッシュされるためディスクI/Oはほとんど発生しません。DBチューニングでは、この状態を目指していきます。DBサーバはメモリを増設すればそれでOK、と言う人が多いのはこのためです。それ以外にも、データ型の見直し(文字列→数値など)などによってデータサイズを小さくすることで、同じメモリサイズでもより多くのレコードをキャッシュできるようにすることも非常に効果的です。1個の巨大なテーブルあたり、20-40%程度のデータサイズ縮小ができることは珍しくありません。またIOPSを減らすという観点では、大量レコードをスキャンしないと返せない処理(件数取得とか)において、サマリーテーブルを用意してインデックスからのルックアップ一発で済ましたり、memcachedなどにキャッシュしておいてそもそもDBサーバにアクセスさせない、といった手も有効です。すでにさまざまな手立てを行なっている方が多いと思います。
　インデックス戦略も性能に決定的な影響を与えます。現実的な用途では、ルートとブランチはキャッシュされやすいので、多くてもリーフと実レコードの2回のランダムI/Oが発生すると見て良いでしょう。ただしこれはインデックス経由でレコードを1個だけ取る場合の話です。範囲検索によって10レコードを取る場合は、リーフ1回に対して実レコード10回の、計11回のランダムI/Oが発生し得ます。SQLはシンプルなのになんで遅いの?という質問を受けることがよくありますが、SQLがシンプルかどうかは関係なく、どれだけのI/Oが発生するかがポイントになります。ディスクI/O性能問題を解決する上で、インデックス戦略の見直しは大きなインパクトがあります。このあたりの詳細なテクニックは、今後さまざまな機会で取り上げていきたいと思います。