最適化したはずなのに性能が出ないデータベースが生まれる意外な理由：Database Watch（2015年9月版）

「あれ？遅いぞ」――データベースの性能で問題が生じたとき、原因を追求すると予想外の問題にたどり着くことがあります。今回は、データベース側が「良かれ」と思って提供する機能が思わぬ問題を生じさせたケースの話題を聞いてきました。

» 2015年10月01日 05時00分公開

　「DB2」ユーザーのための勉強会「Club DB2」が2015年9月18日に開催されました。勉強会の講師は日本アイ・ビー・エムシステムズ・エンジニアリングの白井徹哉氏です。本稿ではデータベースの性能改善ノウハウをDB2を例に見ていきます。

日本アイ・ビー・エムシステムズ・エンジニアリングの白井徹哉氏

　勉強会のお題は「性能劣化のトラブルシューティング」。データベース管理者からすると、トラブル解決を命じられると胃が痛くなるようなストレスでしょうが、見方を変えればスキルを高めるチャンスでもあります。トラブル解決のプロセスは、システムがどのような仕組みで動いているかを考えながら原因を探っていきますから、全体を把握しつつ、細部まで深く掘り下げる必要があり、経験が知識につながります。

　筆者もエンジニアのときはトラブルシューティングから多くを学びました。とはいえ、状況が厳し過ぎる場合は、精神的にも体力的にもきついものです。

　さて、データベースが性能を劣化させる原因は多岐にわたります。一般的には、性能の劣化を検知すると、アプリケーションのログや各種管理ツールの情報を手掛かりに原因を探っていきます。そこで「処理時間の長いSQL」「読み取り行数の多いSQL」が見つかれば、対策すべきポイントに目星を付けられます。

問題があるSQLを特定する

　この日、性能劣化の原因を探るための道具として白井氏が紹介したのは「monreport.pkgcacheプロシージャ」です。パッケージキャッシュに保管されている情報をリポートするDB2固有のプロシージャで、コマンドラインから利用できます。

　DB2ではSQLを実行するとき、そのSQLをシステム側でコンパイルし、キャッシュにためる処理を行います。同じSQLを再度発行された場合に効率よく再利用できるようにするためです。コンパイルしてキャッシュするのと同時に、使用したCPU時間、待機時間、処理された行数なども記録します。その記録を出力するのがこのmonreport.pkgcacheプロシージャです。実行する際はコマンドラインから次のように呼び出します。

$ db2 "call monreport.pkgcache()"

　monreport.pkgcacheプロシージャを呼び出すと、CPU時間が多い順に10個のSQLステートメントを列挙して出力します。簡単に言えば「重たいSQLのワースト10」です。他にも待機時間、読み書き行数、実行回数別に書き出すこともできます。

　このコマンドは簡単に実行できるのですが、SQLステートメントのテキストが冒頭から60文字までしか表示されないのが難点です。SQLステートメントを最後まで取得するには「MON_GET_PKG_CACHE_STMT」関数をSQLのSELECTリストから使います。なお、上記のmonreport.pkgcacheプロシージャも内部的にはMON_GET_PKG_CACHE_STMTを用いています。

* ちなみに、プロシージャと関数の違いは戻り値をとるか否かの違いによります。プロシージャは戻り値を取らないものを指しますので、書き分けています。

　白井氏は「分析したりグラフを用いてリポートしたりする場合にも便利なので、“MON_GET_”で始まる関数を実行するときは出力先をCSVにして、結果を表計算ソフトで確認するのがオススメ」とアドバイスしていました。

　これまで述べた方法はコマンドライン操作なのでちょっと難度が高いかもしれません。参考までにDB2には「DB2 Workload Manager」という管理ツールもあります。このツールを使えば「処理時間が1分を超えるもの」など、しきい値を超えたものの情報を自動的に収集することができます。常時管理ツールで監視しておくべきでしょうね。

適切な経路を導き出すには適切な地図が必要

　では問題があるSQLを特定できたとしましょう。システム全体ではなく特定のSQLだけが遅いことが分かり、再現性もあるとします。そこまで分かれば、次は「そのSQLのどこに問題があるか」を探ります。

　ここで「最適化プログラム（オプティマイザー）」と「アクセスプラン（SQL実行計画）」について述べておきましょう。この二つはDB2に固有のものではなく、昨今のリレーショナルデータベースシステムでは一般的な機能実装です。最適化プログラムでは、SQLを実行する際、システムが持つデータの統計情報から、最も効率の良いアクセスプランを見積もることができます。

　統計情報を地図、SQLの実行結果を目的地と考えるとイメージしやすいかもしれません。統計情報という地図から、目的地というSQL実行結果までの最適な経路（アクセスプラン）を考えるのが最適化プログラムです。

　白井氏は「システムに適切なアクセスプランを選択させるためには、正しい統計情報が必要」と強調します。当たり前のことのようですが、なぜ強調しなければならないのでしょう？

なぜ統計情報が間違っている状況が発生するのか

　実は、SQLで問題が生じている時、「アクセスプランを導き出すための資料である統計情報が実情と異なっていた」という可能性が、一定の確率であり得るといいます。「そもそもの地図が正しくなかったので不適切な経路で目的地に向かってしまった」という状況です。

　それでは、「統計情報が間違っている」というのはどのような状況かを考えてみましょう。

　DB2の場合、統計情報は「RUNSTATS」を実行することで作成されます。統計情報でトラブルが発生するのは、例えば「RUNSTATS」が実行されてからテーブルが持つデータが変化してしまったというような状況が考えられます。

　白井氏は実例として、ある問題解決のストーリーを教えてくれました。

　その実例では本番稼働後、急に著しいI/Oが発生したため、データベース全体のスローダウンが発生していたそうです。原因を調査したところ、あるSQLを実行する際、本来使用するべきインデックス（索引）を用いず、実行するたびにテーブルを全件走査していたことが判明しました。さらに調査を進めると、RUNSTATSを実行したのはデータのない本番稼働前だったということです。

　つまり、このケースでは、テーブルが空の状態のときに取得した統計情報を基にDB2がアクセスプランを見積もっていたため、「索引を使用する必要はない」と判断してテーブルを直接読み取ろうとしていたというわけです。

　このケースではRUNSTATSを再度実行することで、実態に即して最適化されたアクセスプランに置き換わり、性能劣化の問題は解決しました。

　ここではDB2を題材に紹介しましたが、この問題はDB2固有のものではありません。データベースソフトウエアが実行する「最適化プログラムの元データ＝統計情報のズレ」が、全体の性能劣化を引き起こす可能性は、どのデータベースにもあり得ます。

　性能を劣化させる原因は多岐にわたりますから、原因を特定するにはさまざまなデータを集めて判断していく必要がありますが、SQLに問題がある場合には、利用しているRDBMSが実装する最適化プログラムやアクセスプランについての知識があると有利なことが少なからずあります。もちろん、いざというときのために、本稿で紹介したようなツールやコマンドをあらかじめ理解しておくことも重要です。