pg_stat_statements を使って遅いクエリを見つける (PostgreSQL, Ubuntu 10.04)

PostgreSQL などのリレーショナルデータベース (RDB) に対して発行されたクエリのうち、どのクエリで時間がかかっているのかを調べることは重要です。そのための手段は色々ありますが、ここでは PostgreSQL のバージョン 8.4 以降で使用できる pg_stat_statements について、Ubuntu での使い方を説明します。

pg_stat_statements とは

pg_stat_statements とは、PostgreSQL 8.4 以降で使用できるモジュールであり、これを使うことによりサーバーで実行された全ての SQL 文の実行統計を探知することができます。

公式的な説明は以下のページにあります。

pg_stat_statements を Ubuntu 10.04 で使用する

準備

ここでは、Ubuntu 10.04 の apt-get でインストールした PostgreSQL 8.4 で pg_stat_statements モジュールを使うための手順を説明します。 PostgreSQL 8.4 は既にインストールされているものとします。インストールされていない場合は次の apt-get コマンドでインストールしてください。

$ sudo apt-get install postgresql libpq-dev

pg_stat_statements モジュールのインストール

pg_stat_statements モジュールは postgresql-contrib パッケージに含まれていますので、まずはこのパッケージを apt-get でインストールします。

$ sudo apt-get install postgresql-contrib

pg_stat_statements モジュールを使用するように postgresql.conf を書き換える

pg_stat_statements モジュールを使用するように、/etc/postgresql/8.4/main/postgresql.conf を書き換え、shared_preload_libraries に pg_stat_statements を追加します。

# shared_preload_libraries = '' # 元々こういうコメント行があるので, 下の行を追加する
shared_preload_libraries = 'pg_stat_statements'

また、pg_stat_statements モジュールに関するいくつかの設定項目も postgresql.conf に書くことができます。詳細は pg_stat_statements (PostgreSQL 8.4 Documentation) をご覧ください。

postgresql.conf を書き換えた後、PostgreSQL の再起動を行います。

$ sudo service postgresql-8.4 restart

これで、pg_stat_statements による SQL 文実行時の実行統計が保持されるようになります。

pg_stat_statements ビューを DB に追加

pg_stat_statements モジュールによって実行統計が保持されるようになったわけですが、実行統計を閲覧するために pg_stat_statements ビューを DB に追加する必要があります。 pg_stat_statements ビューを DB に追加するための SQL は /usr/share/postgresql/8.4/contrib/pg_stat_statements.sql というファイルで提供されていますので、このファイルの内容を SQL として実行します。

この作業は、実行統計を閲覧する対象となる各 DB でそれぞれ行わなければいけません。ここでは、例として test_db に pg_stat_statements ビューを追加します。なお、既に test_db という DB は作成済みであるものとします。

$ sudo -u postgres psql -d test_db -f /usr/share/postgresql/8.4/contrib/pg_stat_statements.sql

このとき, postgres ユーザーで実行するようにしなければいけないようです。

実行統計の閲覧

後は、しばらく DB を使用して統計情報を溜めて、必要に応じて pg_stat_statements ビューに対して SELECT を実行して実行統計を見てください。 calls という列はその SQL 文が発行された回数を表し、total_time という列はその SQL の実行に要した総時間です。すなわち、1 回の実行あたりにかかった時間の平均は total_time / calls で計算できます。

その他、詳しいことは pg_stat_statements (PostgreSQL 8.4 Documentation) をご覧ください。

問題への対処

私が遭遇した問題と、その問題に対する対処法を書いておきます。

SHMMAX の設定

pg_stat_statements は別途共有メモリを必要とするため、pg_stat_statements を使用するようにすると、「システムの SHMMAX の値が小さすぎるか、PostgreSQL の shared_buffer の設定サイズが大きすぎるか、PostgreSQL の max_connections の設定値が大きすぎる」というようなエラーが発生して PostgreSQL を起動できなくなる可能性があります。その場合は、システムの SHMMAX の値を大きくしてやる必要があります *1。

SHMMAX の値を確認するには、ipcs コマンドを使います。また、SHMMAX の値はファイル /proc/sys/kernel/shmmax に書かれているため、このファイルの中身を見ることでも確認できます。

$ ipcs -l
  # 「共有メモリの制限」 の 「セグメントサイズの最大」 が SHMMAX の値

SHMMAX の値を一時的に設定するには、sysctl コマンドを -w オプションを付けて実行します。

$ sysctl -w kernel.shmmax=XXXXXXX
  # これで設定すると再起動で元に戻ってしまう

上の方法だとシステムを再起動すると元に戻ってしまいます。システムを再起動しても元に戻らないようにするには、/etc/sysctl.conf ファイルに以下の行を追加します。 (XXXXXX は設定したい SHMMAX の値。)

kernel.shmmax=XXXXXX

システムを再起動するか、-i オプションを付けて sysctl コマンドを実行すればファイルに書かれた値になります。

古いバージョンだと発生するエラー

PostgreSQL 8.4.1 だと、pg_stat_statements ビューに対して SELECT を行うクエリを発行すると、2 回に 1 回ぐらいの頻度で以下のエラーが発生しました。

サーバとの接続が想定外にクローズされました
        おそらく要求の処理前または処理中にサーバが異常終了
        したことを意味しています。
サーバへの接続が切れました。リセットしています: 失敗。

これはどうやらバグのようで、PostgreSQL 8.4.7 にすると直りました。

*1:SHMMAX をそれ以上大きくできないのであれば、shared_buffer のサイズや max_connections の値を小さくする必要がある。