遅い SQL を見つけ出すといっても、アプリケーションから実行されるすべての SQL を 1 つ 1 つ調べていくのはたいへんです。 ここでは、ログに SQL を出力して見つけ出す方法と、pg_stat_statements モジュールを使って見つけ出す方法について紹介します。

まず、ログに SQL を出力して遅い SQL を見つけ出す方法についてです。 単純に実行に時間のかかっている SQL をログに出力する場合には、設定ファイル postgresql.conf を編集して log_min_duration_statement パラメータを設定します。

log_min_duration_statement = 1s

log_min_duration_statement パラメータは、SQL の実行に指定した時間以上かかった場合に SQL とその実行時間をログに出力するパラメータです。 デフォルトでは -1 (無効) になっています。 上記のように 1s と指定すると、実行に 1 秒以上かかった SQL とその実行時間がログに出力されるようになります。

2011-01-31 00:41:18 JST [1447] LOG:  duration: 9662.281 ms  statement: SELECT * FROM pgbench_accounts JOIN pgbench_branches USING (bid) JOIN pgbench_tellers USING (bid);

log_min_duration_statement パラメータにあまり短い時間を設定してしまうと、大量の SQL がログに出力されて性能が低下してしまうので、長めの時間を設定してログへの出力を見ながら徐々に短くしていくのがいいでしょう。

ただ、log_min_duration_statement パラメータの設定のみでは、実行時間の長い SQL を個別に探すことはできますが、実行時間がそこそこ長くて実行回数の多い同じパターンの SQL を見つけ出すのには向いていません。 そのような SQL を見つけ出すには、pgFouine などのツールを使ってログを解析するか、PostgreSQL 8.4 (PowerGres 7) 以降で追加された pg_stat_statements モジュールを使うのいがいいでしょう。 pg_stat_statements モジュールについては次項から詳しく紹介していきます。

pg_stat_statements は、SQL の実行回数や実行時間などの統計情報を収集するモジュールです。 収集された統計情報はビューを通して参照できます。 例えば、実行回数の多い SQL や実行時間の長い SQL を調べるのにとても便利です。

F.29. pg_stat_statements (PostgreSQL 8.4 日本語マニュアル)

https://www.postgresql.jp/document/8.4/html/pgstatstatements.html

Windows に One Click Installer を使って PostgreSQL をインストールしている場合には、デフォルトで pg_stat_statments モジュールがインストールされているので、あとは pg_stat_statments モジュールを使えるように設定するだけです。

Linux では、PostgreSQL がインストールされていても pg_stat_statements モジュールがインストールされていない場合があります。 RPM パッケージを使って PostgreSQL をインストールしている場合には、以下のコマンドを実行して postgresql-contrib パッケージがインストールされているかを確認してください。

$ rpm -q postgresql-contrib
postgresql-contrib-8.4.6-1PGDG

上記のようにパッケージの情報が表示されれば、postgresql-contrib パッケージはインストールされています。 逆に、以下のようなメッセージが出力された場合には、postgresql-contrib パッケージはインストールされていないので、使っているディストリビューション向けのパッケージをインストールする必要があります。 各ディストリビューション向けのパッケージは こちら からダウンロードできます。

$ rpm -q postgresql-contrib
パッケージ powergres-contrib はインストールされていません。

ソースコードをビルドして PostgreSQL をインストールしている場合には、pg_config コマンドでライブラリの格納先を調べ、そこに pg_stat_statements.so という共有ライブラリがあるかを確認してください。

$ pg_config --libdir
/usr/local/pgsql/lib
$ find /usr/local/pgsql/lib -name pg_stat_statements.so
/usr/local/pgsql/lib/pg_stat_statements.so

pg_stat_statements モジュールの共有ライブラリがない場合には、ソースコードをビルドしてインストールする必要があります。 もし、PostgreSQL のビルド時に展開したソースコードのディレクトリが残っていれば、そのディレクトリに移動して以下のコマンドを実行してください。 ここでは、ソースコードが /usr/local/src/postgresql-8.4.6 ディレクトリに展開されているものとします。

$ cd /usr/local/src/postgresql-8.4.6
$ make -C contrib/pg_stat_statements
(省略)
$ su
パスワード:(root ユーザのパスワードを入力)
# make -C contrib/pg_stat_statements install
(省略)

なお、ソースコードを展開したディレクトリを削除してしまっていても大丈夫です。 その場合には、あらためて PostgreSQL のソースコードをダウンロードして展開し、make コマンドの実行時に USE_PGSX=1 と指定します。 PostgreSQL のソースコードは こちら からダウンロードできます。

$ tar -xjf postgresql-8.4.6.tar.bz2
$ cd postgresql-8.4.6
$ make -C contrib/pg_stat_statements USE_PGXS=1
(省略)
$ su
パスワード:(root ユーザのパスワードを入力)
# make -C contrib/pg_stat_statements USE_PGXS=1 install
(省略)

PowerGres の場合には、PostgreSQL と違って Windows だけでなく Linux でもデフォルトで pg_stat_statements モジュールがインストールされています。

PostgreSQL の場合には、設定ファイル postgresql.conf を編集してデータベースサーバを再起動し、SQL スクリプトを実行して pg_stat_statements の関数とビューを作成する必要があります。

1. 設定ファイル postgresql.conf を編集します。

   以下の太字になっている行を追加してパラメータを設定します。

   (省略)
   #------------------------------------------------------------------------------
   # RESOURCE USAGE (except WAL)
   #------------------------------------------------------------------------------
   (省略)
   # - Kernel Resource Usage -
   
   #max_files_per_process = 1000           # min 25
                                           # (change requires restart)
   #shared_preload_libraries = ''          # (change requires restart)
   shared_preload_libraries = 'pg_stat_statements'
   (省略)
   #------------------------------------------------------------------------------
   # CUSTOMIZED OPTIONS
   #------------------------------------------------------------------------------
   
   #custom_variable_classes = ''           # list of custom variable class names
   custom_variable_classes = 'pg_stat_statements'
   pg_stat_statements.max = 1000
   pg_stat_statements.track = top
   pg_stat_statements.save = on
   

   各パラメータの説明は以下のとおりです。

   パラメータ名	説明
   shared_preload_libraries	データベースサーバの起動時に読み込む共有ライブラリの名前をカンマ区切りで指定するパラメータです。 pg_stat_statements モジュールの共有ライブラリを読み込むため、パラメータの値には「pg_stat_statements」と指定します。
   custom_variable_classes	設定ファイル postgresql.conf に独自のパラメータを記述するとき、そのパラメータのクラス名をカンマ区切りで指定するパラメータです。 pg_stat_statements モジュールのパラメータを記述するため、パラメータの値には「pg_stat_statements」と指定します。
   pg_stat_statements.max	pg_stat_statements モジュールで収集する SQL の最大数を指定するパラメータです。 SQL の数がこのパラメータの値を越えた場合には、実行回数の少ない SQL から順番に統計情報が削除されます。
   pg_stat_statements.track	pg_stat_statements モジュールで収集する SQL の種類を指定するパラメータです。 パラメータの値に「top」と指定するとクライアントから直接実行された SQL のみ、「all」を指定すると関数内から間接的に実行された SQL も含めて収集されます。 pg_stat_statements モジュールを無効にするには、このパラメータの値に「none」と指定します。
   pg_stat_statements.save	pg_stat_statements モジュールが収集した統計情報をデータベースサーバの停止時に保存するかを指定するパラメータです。

2. データベースサーバを再起動します。

3. pg_stat_statements モジュールの関数とビューを作成します。

   関数とビューを作成するには、pg_stat_statements.sql という SQL スクリプトを実行します。 pg_stat_statements.sql スクリプトは、通常、PostgreSQL のインストール先の share/contrib ディレクトリ内に含まれています。 このスクリプトを psql コマンドの -f オプションで指定して実行します。

   $ psql -f /usr/local/pgsql/share/contrib/pg_stat_statements.sql testdb
   SET
   CREATE FUNCTION
   CREATE FUNCTION
   CREATE VIEW
   GRANT
   REVOKE

PowerGres の場合には、pg_stat_statements モジュールの設定を PowerGres Manager から簡単に行えます。

1. サーバ管理ツリーメニューの「SQL 実行履歴」をクリックします。

   

2. 「pg_stat_statements モジュールをロードする」にチェックを入れ、pg_stat_statements モジュールの各設定項目の入力後に「適用」をクリックします。

   

   各設定項目は pg_stat_statements モジュールのパラメータにそれぞれ対応しています。 詳しくは パラメータの説明 を参照してください。

   設定項目	パラメータ名
   	pg_stat_statements.max
   	pg_stat_statements.track
   	pg_stat_statements.save

3. サーバ管理ツリーメニューの「サービス」をクリックし、「サービスを再起動」をクリックしてデータベースサーバを再起動します。

   

4. サーバ管理ツリーメニューの「SQL 実行履歴」をクリックすると、確認を求めるダイアログが表示されるので、「OK」をクリックして pg_stat_statements モジュールの関数とビューを作成します。

   

pg_stat_statements モジュールの関数とビューが作成されると、SQL の統計情報を pg_stat_statements ビューを通して参照できるようになります。 psql コマンドでデータベースに接続し、以下の SQL を実行して統計情報を参照してみましょう。

=> \x
Expanded display is on.
=> SELECT * FROM pg_stat_statements;
-[ RECORD 1 ]---------------------------------------------------------------------
userid     | 16392
dbid       | 16393
query      | select count(*) from pgbench_branches
calls      | 1
total_time | 2.4e-05
rows       | 1
-[ RECORD 2 ]---------------------------------------------------------------------
userid     | 16392
dbid       | 16393
query      | UPDATE pgbench_branches SET bbalance = bbalance + -451 WHERE bid = 1;
calls      | 1
total_time | 1.3e-05
rows       | 1
-[ RECORD 3 ]---------------------------------------------------------------------
userid     | 10
dbid       | 16393
query      | <insufficient privilege>
calls      | 1
total_time | 0.000234
rows       | 1
(省略)

pg_stat_statements ビューの各列の説明は以下のとおりです。

userid 列と dbid 列はそれぞれユーザ (ロール) とデータベースを表す OID (データベースオブジェクトの識別子) です。 OID ではなくユーザ名やデータベース名で表示したい場合には、以下のように OID から名前に変換してあげるといいでしょう。

SELECT pg_get_userbyid(userid) AS username,
    (SELECT datname FROM pg_database WHERE oid = dbid) AS dbname,
    query, calls, total_time, rows
FROM pg_stat_statements;

rows 列の説明が少し分かりにくいかもしれませんが、これは、SELECT 文の場合には検索された行数、INSERT 文の場合には挿入された行数、UPDATE 文の場合には更新された行数、DELETE 文の場合には削除された行数の合計を表します。

pg_stat_statements モジュールでは、以下の 3 つの SQL のようにパターンが同じで値だけが異なる SQL を同じパターンの SQL と見なして統計情報を収集してくれます。

SELECT abalance FROM pgbench_accounts WHERE aid = 4849;
SELECT abalance FROM pgbench_accounts WHERE aid = 55693;
SELECT abalance FROM pgbench_accounts WHERE aid = 79001;

ただし、値だけが異なる SQL を同じパターンの SQL と見なすのは、拡張問い合わせプロトコルを使って SQL を実行した場合に限られます。 拡張問い合わせプロトコルというのは、簡単に言ってしまうと、SQL の実行時に SQL とそれに埋め込む値を分けて送信するプロトコルです。 拡張問い合わせプロトコルに対して簡易問い合わせプロトコルというプロトコルもあります。 簡易問い合わせプロトコルは値を埋め込んだ SQL を送信して実行するプロトコルです。

psql コマンドでプリペアードステートメントを使わずに SQL を実行した場合には、簡易問い合わせプロトコルとなります。 プログラムから SQL を実行した場合には、簡易問い合わせプロトコルと拡張問い合わせプロトコルのどちらになるかは API に依存します。 とは言え、プログラムで SQL とそれに埋め込む値を分けて指定している場合には、通常、拡張問い合わせプロトコルになっていると考えてよいでしょう。

例えば、PHP では、pg_query 関数を使っている場合には簡易問い合わせプロトコル、pg_query_params 関数を使っている場合には拡張問い合わせプロトコルになります。

PowerGres の場合には、pg_stat_statements モジュールの設定だけでなく統計情報の参照も PowerGres Manager から行えます。 もちろん、PostgreSQL と同じように pg_stat_statements ビューに対して SELECT 文を実行して参照することもできます。

PowerGres Manager から SQL の統計情報を参照するには、サーバ管理ツリーメニューの「SQL 実行履歴」をクリックします。 続いて、「表示」タブをクリックすると、SQL の統計情報が表として表示されます。 ちなみに、表の列をクリックすると、その列をキーにして並び替えが行えます。

今回は、pg_stat_statements というモジュールを使って遅い SQL を見つけ出す方法について紹介しました。 pg_stat_statements を使うとログを解析する手間をかけずに PostgreSQL の機能だけで遅い SQL を見つけ出せることをご理解いただけたでしょうか。

ただ、遅い SQL を見つけ出すことは性能を改善する上では取っ掛かりに過ぎません。 遅い SQL が見つかったら EXPLAIN 文を使って原因を調べ、パラメータの変更やインデックスの追加などの改善を行っては効果を確認することを繰り返していく必要があります。

PowerGres 体験記はひとまず今回で最終回となります。 PostgreSQL および PowerGres を使う上で知っておいていただきたいことはまだまだたくさんあります。 今回紹介しきれなかった、EXPLAIN 文を使った遅い原因の調査方法や性能の改善方法については、またの機会に紹介できればと考えています。