さて、今回はデータベース関係の話題をご提供いたします。最近、大量データを前提とした検索機能の実装プロジェクトがあり、そのためのパフォーマンス検証を行うという、ややQA寄りの活動をしていました。その中で対応として検討したことや学んだことを書いてみたいと思います。なお、データベースは PostgreSQL を前提とした内容です。
かなりまとまりないというか寄せ集め感満載ですが、目次は以下のとおりです:
かなりまとまりないというか寄せ集め感満載ですが、目次は以下のとおりです:
- EXPLAIN の結果を読む
- 中間一致のためのインデックス
- 必要な対象だけに絞り込むためのフラグ
- NULL をなくす
- 遅いSQLが発生してしまったときのために
■ EXPLAIN の結果を読む
まず、データベースでパフォーマンス関係のことをしようと思ったとき必須になるであろう、 EXPLAIN について簡単に。
基本をおさえるために、以下のページからリンクされている資料が参考になります。
Explaining Explain 〜 PostgreSQLの実行計画を読む 〜 (PDF版)
また、ツールとしては PgAdmin を利用すると、EXPLAIN の結果をグラフィカルに表示してくれるので、コンソール上で細かい出力結果を文字でみるよりは見やすいかもしれません。
Reading PgAdmin Graphical Explain Plans (英語)
さらに、どの部分の実行で時間がかかっているのかを視覚的に表示してくれるオンラインツールもあります。EXPLAIN の結果を貼り付けると、一番時間がかかっている箇所を赤く表示してくれたりします。そのまま実行するとテーブル名やカラム名が保存されてしまうので、プロダクトのDBでの結果を使う場合などは念のため "I want this plan to be anonymized before saving." にチェックを入れて匿名化されるようにしたほうがよいでしょう。
explain.depesz.com
■ 中間一致のためのインデックス
大量データの中に含まれるテキストデータに対して中間一致で検索をかける場合、そのままではインデックスが効きません。
PostgreSQL 9.1 以降では、 「pg_trgm」を使う方法が一番有力な候補です。
PostgreSQL 9.1 以降では、 「pg_trgm」を使う方法が一番有力な候補です。
参考:
ストリーミングレプリケーションにも対応しており、LIKE検索もそのまま使えるので、アプリケーション側でSQLを変えるなどの必要がありません。
ただ、 pg_trgm で日本語に対して有効なインデックスを作るためには locale を ja_JP にする必要があるようで、既存のデータベースが locale = C で作成されている現状にあってはその点をどうするか問題となっており、まだ答えは出ていません。
このあたりの中間一致のためのインデックスについては、他の手法などと比べてのメリット・デメリットなど、別記事に書こうと思います。
■必要な対象だけに絞り込むためのフラグ
当初は、大量データの全体に検索をかけることを想定して上記のようなインデックスの方法を調査していましたが、今回私達が対象とする機能では、膨大となる量のデータのうち、本当に必要なレコードは一部のものに限られることが分かりました。当時、その区別をつけるためのデータがなかったので、フラグとなるカラムを追加し、それに対するインデックスも作成するようにしました。
これにより、全体のレコード数が膨大になるテーブルに対しても、まずこのフラグのインデックスでデータが絞り込まれるため、シーケンシャルスキャンにならないことが期待できるようになりました。
実際の機能ではこのように、検索のために本当に必要なデータは何であり、それに限定するための手段がないかを見極めるのは重要かと思います。
■NULL をなくす
NULL の害悪については、RDB界隈においては度々言及されているようです。
参考:
- http://www.geocities.jp/mickindex/database/db_getout_null.html
参考:
- http://www.geocities.jp/mickindex/database/db_getout_null.html
検索においては特に、以下の点が問題になります。
- (たとえば空白文字による検索などで)カラの場合と NULL の場合を考慮する必要がある
- IS NULL の検索でインデックスが効かない
ただし、実際には注意も必要です。既存のカラムを変更した場合、今までは既存の機能で NULL を含むレコードが検索結果に含まれるようになるなど、挙動が変わる場合があります。
新規に追加するカラムの場合も、そのカラムの持つ意味によっては集計や検索で注意が必要でしょう。(たとえば、ゼロのレコードを含んだまま平均をとってしまう、など)
■遅いSQLが発生してしまったときのために
なるべく遅いSQLが発生しないように事前準備をしていたとしても、データの増加ペースや分布が想定外となるなど、実際は遅いSQLが発生してしまうリスクはゼロにはできません。実際に発生してしまったときのために、以下のような機能を利用する選択肢もあります。
-- 遅いSQLをログに残す
PostgreSQL では以下のコンフィグを設定することで、一定の時間がかかったSQLだけをログに残すことができます。
- たとえば 10秒こえたSQLだけ出すためには
- $PG_DATA/postgresql.conf に以下のように書く
- log_min_duration_statement = 10s
- ついでにログのプレフィックスにDB名とリモートホスト出す
- log_line_prefix = '[%d,%r] '
- ちなみに 9.0以降では %a で application_name も出せる(アプリ側から SET 可能)
-- 遅いSQLの実行をキャンセルする
こちらは機能寄りの内容になりますが、SQL実行のタイムアウト値をセットすることで、それ以上時間がかかった場合は実行をキャンセルすることができます。DBセッション中でセット＆リセットができるので、実行を途中でキャンセルしても問題ない機能などでは、限定的に利用を検討してもよいかと思います。
- たとえば、10秒を超えたら実行キャンセルしたい場合
- SET statement_timeout TO 10000;
- SELECT * FROM hoge WHERE 〜〜〜; // ← これが 10秒経って終わってなかったら自動的にキャンセルされます
- RESET statement_timeout;
■ おわりに
以上、乱雑ではありますが、PostgreSQL を使って大量データ検索機能を作る場合に実際に調べたことや検討したポイントなので、何かの参考になれば幸いです。
