PREPARATION
受験対策

今回も前回に引き続きパフォーマンスのチューニングをテーマにしてお伝えしていきます。
後編では

・実行計画
・バキューム処理、統計情報の収集
・スロークエリの探し方

を紹介します。

前回の最後にインデックスの効果を見る為にEXPLAINを使って実行計画を参照しました。
今回も同じようにsalesテーブルに対してWHERE句はつけない形での実行計画を見てみます。

以下のSQLを実行してください。
EXPLAIN SELECT * FROM sales;

表示された結果が実行計画です。ここで示されているのは実際に問い合わせを行った結果ではなく、統計情報を元にした推定値であることに注意してください。また、統計情報はテーブルの全行を検査しているのではなく、ランダムにサンプリングしたデータを元にしています。

出力された数値の意味を以下に記述します。

①と②の数値はコストを表しています。ほとんどの問い合わせの場合、重要な意味を持つのは②のトータルコストになるでしょう。

③は得られる結果の推定行数です。実際に問い合わせを行った場合と異なる場合もありますが、その差が大きい場合には統計情報と実データの状態が乖離していると考えられますので、ANALYZEを実行して統計情報の更新を行いましょう。（ANALYZEコマンドの詳細は後述します）

④は行あたりの長さを表しています。問い合わせ結果の容量を見積もる場合などにこの値は有用です。

次にEXPLAINにANALYZEオプションを付与して実行してみましょう。
ANALYZEオプションを付与した場合は実際に問い合わせが実行され、その実測値も併せて出力されます。

これによって推定値と実測値の精度をチェックすることができます。かけ離れている場合は後述するANALYZEを実行し、統計情報の更新を行います。

EXPLAIN ANALYZE SELECT * FROM sales;

ANALYZEオプションを付与すると出力される情報が増えました。それぞれの数値の意味を以下に記述します。

PostgreSQLを運用していく上でバキューム処理は欠かせないものです。これはPostgreSQLがどのようにデータを管理しているかに密接に関わっていますので、VACUUM処理の解説の前に簡単に触れておきたいと思います。

PostgreSQLでは同時実行制御方式として追記型MVCCという方式を採用しています。
追記型ではデータが更新されたり削除された場合でも実際にディスクから削除を行わず、削除マークを付けることで読み取り対象外とする仕組みとなっています。この為、更新・削除処理を繰り返し行うと不要なデータも増えていきます。不要なデータが増え続ければディスク容量を圧迫しますし、シーケンシャルスキャンを行う際にはより多くのディスクIOが発生し、パフォーマンス低下の原因になります。

そこで利用されるのがバキューム処理です。バキューム処理は不要なデータ領域を回収し、再利用可能な状態にします。
実行方法を以下に記載します。

・VACUUM [テーブル名]
テーブル名を省略した場合は全テーブルに対してバキューム処理が行われます。
ただし、使用しているディスク容量は変化しません。不要データの回収と併せてディスク使用量の削減まで行う場合にはFULLオプションを付与して実行します。

・VACUUM FULL [テーブル名]
FULLオプションを付与することで他のブロックに対してデータの移動が行われ、使用ブロック数を削減することができます。
この処理中は他のユーザーが読み書き不可となる為、実行するタイミングには注意が必要です。

・VACUUM ANALYZE [テーブル名]
バキューム処理を行った後、統計情報を収集します。

・ANALYZE [テーブル名]
統計情報の収集を行います。
大量のデータを追加、削除した場合やインデックスを追加した直後などには明示的に実行すると良いでしょう。

現在のバージョンではデフォルトで自動バキューム処理と併せて統計情報の収集も行われるようになっています。
ユーザーの手で明示的にバキューム処理を実行する必要は減りましたが、重要な機能ですので仕組みと共に覚えておいてください。

●統計情報ビューを使う

使いはじめるには前準備が必要になりますが、SQLで検索が可能なので後述するサーバーログを用いる方法よりも使いやすいのが特徴です。
まず"pg_stat_statements"というエクステンションを追加しますが、その為にスーパーユーザー（postgres）になってからossdbデータベースに接続します。

エクステンションを追加するには以下のSQL文を実行してください。

CREATE EXTENSION pg_stat_statements;

次にpostgresql.confを編集します。

以下の2つの項目を設定します。
（初期値では両方コメントアウトされていますので、アンコメントした上で値を設定してください。）

shared_preload_libraries = 'pg_stat_statements'
custom_variable_classes = 'pg_stat_statements'

編集完了後、PostgreSQLの再起動を行います。

psqlでossdbデータベースに接続し、以下のSQL文を実行してみてください。

SELECT query, calls, total_time FROM pg_stat_statements ORDER BY total_time LIMIT 3;

導入直後の状態では統計情報が溜まっていませんが、しばらく運用してから実行してみると有用な結果が得られると思います。
pg_stat_statementsが返す項目の内、今回はquery、calls、total_timeだけを抽出しています。

適当なSQLを実行した後、ログファイルを開いてみましょう。ログファイルは/var/log/postgresql/postgresql-9.1-main.logにあります。ファイルを開いて確認してみると、実行したSQLが処理時間と共に記録されているはずです。

ただ、この設定では全てのSQLをログに記録する為、ログ出力のオーバーヘッドが大きく、パフォーマンスを劣化させてしまう原因になります。今回はスロークエリを発見することが目的ですので、実行に一定以上の時間がかかったSQL文を見つけるようにしたいと思います。例えば3秒以上かかったSQL文だけをログに出力したい場合には

log_min_duration_statement = 3s

と設定します。
設定を変更したら先ほどと同様にPostgreSQLのリロードを行なってください。

今回は実行計画の読み方からバキューム処理、統計情報の収集、スロークエリの見つけ方までをご紹介しました。
まずスロークエリを抽出できるような設定にした上で運用し、パフォーマンスに問題が発生したときに手がかりを掴めるようにしておくことが重要です。

想定外に遅いクエリを抽出
↓
実行計画を確認
↓
必要に応じて

・インデックスを追加
・統計情報の収集
・SQLをもっと効率の良い方法に変えられないかを検討する

このようなサイクルを回してパフォーマンスを劣化させているクエリを改善することで全体のパフォーマンスを向上させていきましょう。

今回が10回目の連載となり、PostgreSQLの機能をご紹介する最後の回となりました。
次回からはブラウザからPostgreSQLを利用する例を、簡単なWebアプリケーションを通じてご紹介していく予定です。