PREPARATION
受験対策

OSS-DB入門

第10回 パフォーマンス・チューニング(後編)

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

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

を紹介します。

実行計画

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

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

出力された数値の意味を以下に記述します。
初期処理の推定コスト
 全体推定コスト
 問い合わせ結果の推定行数
 行の推定平均サイズ(バイト単位)

 

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

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

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

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

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

EXPLAIN ANALYZE SELECT * FROM sales;
ANALYZEオプションを付与すると出力される情報が増えました。それぞれの数値の意味を以下に記述します。
 初期処理の実測時間(ミリ秒)
 全体の実測時間(ミリ秒)
③   結果の行数
 処理実行回数

この値が1よりも大きい場合、①、②、③の値は1実行あたりの平均となる。

①、②については推定コストとは単位が異なる為、単純な値の比較はできませんので注意してください。

なお、INSERT、UPDATE、DELETEのようなデータの変更を伴う問い合わせの実行計画を得たい場合には以下のように実行します。

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

このように実行計画の問い合わせの前にBEGIN、後にROLLBACKを実行することで、実際にデータを更新せずに実行計画を取得することができます。

実行計画についての更に詳細な情報はオンラインマニュアルに記載されています。
http://www.postgresql.jp/document/9.1/html/using-explain.html

バキューム処理、統計情報の収集

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だけを抽出しています。
 query  実行したSQL文の文字列
 calls  実行回数
total_time   処理時間(単位は秒)

 

実行回数の累積値になので、実行回数で除算すれば1回あたりの平均処理時間を求められる。

上記以外の項目についてはオンラインマニュアルを参照してください。
http://www.postgresql.jp/document/9.1/html/pgstatstatements.html

サーバーログを使う

postgresql.confを開いてlog_min_duration_statementの設定値を編集します。デフォルトではコメントアウトされているのでアンコメントします。値は初期値で-1(無効)となっていますので値も変更します。0にすると全てのSQLをログに出力します。設定を変更し保存した後、PostgreSQLをリロードします。(再起動の必要はありません。)

適当な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アプリケーションを通じてご紹介していく予定です。

執筆:
市脇 康二 氏(OSS-DB Gold認定者)株式会社オークニージオソリューション事業部 マネージャー
PostGISを含めたFOSS4G(Free Open Source Software for Geospatial)ソフトウェアを活用し、地図やGPS等の位置情報を組み合わせたアプリケーションやサービスの設計・開発に従事。
サービスプラットフォームがクラウドへ、利用シーンがモバイルへと広がりを見せる中、位置情報の更なる活用シーンを模索中。

アドバイザー:
森 亮 氏 株式会社オークニー代表取締役
2002年にオークニーを設立し、代表取締役に就任。マッピング・ GIS・LBS・ITS関連プロジェクトのビジネスコンサルティングを経て、現在は会社経営の傍ら、『入門Webマッピング』(Tyler Mitchell原著;オライリー・ジャパン発行)の翻訳、Open Source Geospatial財団日本支部の代表者を務めるなど、FOSS4G(オープンソース地理空間ソフトウェア)の普及活動に取り組んでいる。

監修:
松田 神一

LPI-Japan
Platinum Sponsors

関連資格