★悩み★
・PostgreSQLのpg_stat_statementsをインストールして有効化する手順は？
・PostgreSQLのpg_stat_statementsとは何？どういう場合で使うの？
・遅いクエリ(スロークエリ )をpg_stat_statementsから確認できる？
こういった「悩み」に答えます。
★本記事の内容★
① pg_stat_statementsとは？【概要・機能・利用場面を解説】
② pg_stat_statementsのインストールとセットアップ(有効化)手順
③ PostgreSQLでスロークエリをpg_stat_statementsから確認する手順
④ pg_stat_statementsの補足情報(情報のリセット手順やエラーなど)
これからご紹介する「PostgreSQLのpg_stat_statementsのインストール方法や使い方」を実践したことで、筆者は20分以内で「pg_stat_statementsをインストールし、有効化することで、クエリの実行時間を確認」できました。
記事の前半では「PostgreSQLのpg_stat_statementsとは何か？」「どういった機能を持ち、どのような場面で利用するのか」を解説します。
記事の後半では、利用場面の1つである「pg_stat_statementsを使いスロークエリ (遅いクエリ)を確認する手順」を紹介します。
この記事を読み終えることで、「PostgreSQLのpg_stat_statementsを適切な場面で利用できる」状態になります。
pg_stat_statementsとは？【概要・機能・利用場面を解説】
「pg_stat_statementsとは？」に関してご紹介します。
pg_stat_statementsの概要、機能、利用場面に関して簡単に説明しますね。
pg_stat_statementsの概要を以下に記載します。
★pg_stat_statementsとは？★
PostgreSQLの拡張モジュールです。
PostgreSQLインストール後に、pg_stat_statementsをインストールし、セットアップ(有効化)することで、利用できます。
参照：pg_stat_statementsとは？(公式サイト)
続いて、pg_stat_statementsの機能を以下に記載します。
★pg_stat_statementsの機能★
pg_stat_statementsは、PostgreSQLで実行されたすべてのSQL文のプラン生成時と実行時の統計情報を記録する手段を提供します。
例えば、pg_stat_statementsを有効化すると、下記情報を把握できます。
・特定のSQL文がどのユーザーによって実行されたか
・特定のSQL文が実行された回数
・特定のSQL文の実行時間
参照：pg_stat_statementsの機能(公式サイト)
最後に、pg_stat_statementsの利用場面を以下に記載します。
★pg_stat_statementsの利用場面★
pg_stat_statementsでは、SQL文の統計情報を確認できる為、以下の場面で利用されます。
・実行回数が多いSQL文を調べたいとき
・処理が遅い(スロークエリ )SQL文を調べたいとき
・データ破壊などの危ないSQL文が実行されていないかを調べたいとき
以上が、「pg_stat_statementsとは？【概要・機能・利用場面を解説】」となります。
実行したSQL文(クエリ )の実行時間などを調査したい場合、pg_stat_statementsの利用を検討しましょう。
pg_stat_statementsのインストールとセットアップ(有効化)手順
「pg_stat_statementsのインストールとセットアップ(有効化)手順」に関してご紹介します。
★pg_stat_statementsのインストールとセットアップ(有効化)手順★
手順1：PostgreSQLをインストール
手順2：PostgreSQLの動作確認
手順3：pg_stat_statementsをインストール
手順4：pg_stat_statementsの有効化
手順5：PostgreSQLの設定ファイルを編集
手順6：PostgreSQLの再起動
上記の流れで、「pg_stat_statementsのインストールとセットアップ(有効化)」ができます。
上記の各手順は、以下の日時と環境で動作確認済みです。
PostgreSQLのバージョン：13
動作確認済み日時：2021年9月14日
動作確認済み環境：CentOS Linux release 7.7.1908 (Core)
以降で、上記「pg_stat_statementsのインストールとセットアップ(有効化)手順」の各手順に関してご説明します。
手順1：PostgreSQLをインストール
「手順1：PostgreSQLをインストール」に関してご説明します。
pg_stat_statementsインストール・有効化するためにも、PostgreSQLをインストールしましょう。
「PostgreSQLのインストール手順」に関しては、以下の記事をご覧ください。
既にPostgreSQLをインストールされている方は、本手順を省略してください。
以上で、「手順1：PostgreSQLをインストール」は完了です。
手順2：PostgreSQLの動作確認
「手順2：PostgreSQLの動作確認」に関してご説明します。
PostgreSQLが正常に動作するかを確認しましょう。
「PostgreSQLが起動できるか」を確認し、PostgreSQLが正常に動作することを確認してください。
起動手順に関しては、以下の記事をご覧ください。
以上で、「手順2：PostgreSQLの動作確認」は完了です。
手順3：pg_stat_statementsをインストール
「手順3：pg_stat_statementsをインストール」に関して解説します。
PostgreSQLの拡張モジュールである「pg_stat_statements」をインストールしましょう。
pg_stat_statementsをインストールするために、以下のコマンドを順に実行してください。
# yum install -y postgresql13-contrib
# find / -name "pg_stat_statements.so"
/usr/pgsql-13/lib/pg_stat_statements.so
#
コマンド実行後、「pg_stat_statements.soのPATH」が表示された場合、「正常にpg_stat_statementsをインストールできた」と判断できます。
以上で、「手順3：pg_stat_statementsをインストール」は完了です。
手順4：pg_stat_statementsの有効化
「手順4：pg_stat_statementsの有効化」に関して解説します。
インストールした「pg_stat_statements」を有効化しましょう。
pg_stat_statementsを有効化するために、以下のコマンドを順に実行してください。
# su - postgres
-bash-4.2$ psql
postgres=# select * from pg_available_extensions where name = 'pg_stat_statements';
name | default_version | installed_version | comment
--------------------+-----------------+-------------------+------------------------------------------------------------------------
pg_stat_statements | 1.8 | | track planning and execution statistics of all SQL statements executed
(1 行)
postgres=#
postgres=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
postgres=# select * from pg_available_extensions where name = 'pg_stat_statements';
name | default_version | installed_version | comment
--------------------+-----------------+-------------------+------------------------------------------------------------------------
pg_stat_statements | 1.8 | 1.8 | track planning and execution statistics of all SQL statements executed
(1 行)
postgres=#
コマンド実行後、「CREATE EXTENSION」が表示された場合、「正常にpg_stat_statementsを有効化できた」と判断できます。
以上で、「手順4：pg_stat_statementsの有効化」は完了です。
手順5：PostgreSQLの設定ファイルを編集
「手順5：PostgreSQLの設定ファイルを編集」に関して解説します。
PostgreSQLでpg_stat_statementsを使用するために、PostgreSQLの設定ファイルを編集しましょう。
PostgreSQLの設定ファイルの1つである「postgresql.conf」を編集しましょう。
見直すべき設定値は、「shared_preload_libraries」となります。
↓↓ shared_preload_librariesの設定値を「pg_stat_statements」に変更 ↓↓
shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
上記の設定をすることで、PostgreSQLでpg_stat_statementsを使用できるようになります。
ちなみに、「shared_preload_libraries」の詳細を知りたい方は、以下の公式サイトをご覧ください。
参照：shared_preload_librariesの説明(公式サイト)
以上で、「手順5：PostgreSQLの設定ファイルを編集」は完了です。
手順6：PostgreSQLの再起動
「手順6：PostgreSQLの再起動」に関して解説します。
postgresql.confで書き換えた設定を反映させ、pg_stat_statementsのセットアップを終了させましょう。
postgresql.confの設定値を反映させるために、以下のコマンドでPostgreSQLを再起動してください。
# systemctl restart postgresql-13.service
以上で、「手順6：PostgreSQLの再起動」は完了です。
上記の流れで、pg_stat_statementsのインストールとセットアップ(有効化)ができました。
PostgreSQLでスロークエリをpg_stat_statementsから確認する手順
「PostgreSQLでスロークエリをpg_stat_statementsから確認する手順」に関してご紹介します。
★PostgreSQLでスロークエリをpg_stat_statementsから確認する流れ★
手順1：PostgreSQLでクエリ実行しpg_stat_statementsから処理時間を把握
上記の流れで、「pg_stat_statementsの1つの使い方である、PostgreSQLで実行した遅いクエリ(スロークエリ)の確認」ができます。
上記の各手順は、以下の日時と環境で動作確認済みです。
PostgreSQLのバージョン：13
動作確認済み日時：2021年9月14日
動作確認済み環境：CentOS Linux release 7.7.1908 (Core)
以降で、上記「PostgreSQLで遅いクエリ(スロークエリ)をpg_stat_statementsから確認する流れ」の手順に関してご説明します。
ちなみに、PostgreSQLのログからもスロークエリ(遅いクエリ)を把握できます。手順など気になる方には、以下の記事もオススメです。
手順1：PostgreSQLでクエリ実行しpg_stat_statementsから処理時間を把握
「手順1：PostgreSQLでクエリ実行しpg_stat_statementsから処理時間を把握」に関して解説します。
pg_stat_statementsから実行したSQL文(クエリ )の処理時間を確認してみましょう。
クエリの実行時間を確認するために、以下のPostgreSQL環境でクエリを実行しました。
# su - postgres
-bash-4.2$ psql
postgres=# \c routefinder
routefinder=# \d
リレーション一覧
スキーマ | 名前 | タイプ | 所有者
----------+----------------+----------+----------
public | album | テーブル | postgres
public | route_data | テーブル | postgres
(2 行)
routefinder=# select count(*) from album;
count
----------
17882068
(1 行)
routefinder=#
クエリ実行後、PostgreSQLのpg_stat_statementsを確認してください。
postgres=# \c postgres
postgres=# SELECT * FROM pg_stat_statements order by total_exec_time desc limit 1;
userid | dbid | queryid | query | plans | total_plan_time | min_plan_time | max_plan_time | mean_plan_time | stddev_plan_time | calls
| total_exec_time | min_exec_time | max_exec_time | mean_exec_time | stddev_exec_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtie
d | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_ti
me | wal_records | wal_fpi | wal_bytes
--------+-------+---------------------+----------------------------+-------+-----------------+---------------+---------------+----------------+------------------+------
-+--------------------+--------------------+--------------------+--------------------+------------------+------+-----------------+------------------+-------------------
--+---------------------+----------------+-----------------+--------------------+--------------------+----------------+-------------------+---------------+-------------
---+-------------+---------+-----------
10 | 17048 | 5240202726221676624 | select count(*) from album | 0 | 0 | 0 | 0 | 0 | 0 | 1
| 1408.1272940000001 | 1408.1272940000001 | 1408.1272940000001 | 1408.1272940000001 | 0 | 1 | 0 | 308063 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0
(1 行)
postgres=#
上記のように、「pg_stat_statementsのtotal_exec_time」を確認することで、実行したクエリ(SQL文)にかかった処理時間を把握できます。
上記のpg_stat_statementsテーブルを確認することで、「実行したクエリの中で特に遅いクエリ(スロークエリ)は？」を把握できます。
ちなみに、「pg_stat_statementsの各列に関する詳細を知りたい」方は、以下の公式サイトを確認してください。
参照：pg_stat_statementsの各列について(公式サイト)
以上で、「手順1：PostgreSQLでクエリ実行しpg_stat_statementsから処理時間を把握」は完了です。
上記の流れで、PostgreSQLで遅いクエリ(スロークエリ )をpg_stat_statementsから確認することがきました。
pg_stat_statementsの補足情報
「pg_stat_statementsの補足情報」に関してご紹介します。
【ご紹介事項】
・pg_stat_statementsの統計情報をリセットする方法
・relation “pg_stat_statements” does not existのエラー解決方法
以降で、上記の各項目に関して解説します。
pg_stat_statementsの統計情報をリセットする方法
pg_stat_statementsのビューに格納された情報をリセット(初期化)したい場合、以下のコマンドを実行しましょう。
-bash-4.2$ psql
postgres=# SELECT pg_stat_statements_reset();
上記の「pg_stat_statements_reset()」リファレンスに関しては、以下をご覧ください。
関数名：pg_stat_statements_reset
機能：pg_stat_statementsのビューに格納された情報をリセット(初期化)する
使い方：SELECT pg_stat_statements_reset();
参照：pg_stat_statements_resetのリファレンス(公式サイト)
relation “pg_stat_statements” does not existのエラー解決方法
ごくまれに、以下のエラーが発生する場合があります。
ERROR: relation "pg_stat_statements" does not exist
上記エラーの原因は、「pg_stat_statementsがインストールされていない」です。よって、本記事の序盤でご紹介した「pg_stat_statementsをインストール」を実施してください。
スロークエリを特定する上で、pg_stat_statementsは、非常に便利な機能です。ぜひ、適切に使ってみてください。
ちなみに、「PostgreSQLのアーキテクチャを体系的に学びたい」や「その他のPostgreSQLのコマンドを知りたい」方は、以下の参考書がオススメです。
【まとめ】PostgreSQLのpg_stat_statementsのインストール方法や使い方
いかがでしたでしょうか？
「pg_stat_statementsとは何か？」「pg_stat_statementsのインストールや有効化手順」「pg_stat_statementsを使ったスロークエリ の確認方法」を解説し、以下の悩みを解決しました。
★悩み★
・PostgreSQLのpg_stat_statementsをインストールして有効化する手順は？
・PostgreSQLのpg_stat_statementsとは何？どういう場合で使うの？
・遅いクエリ(スロークエリ )をpg_stat_statementsから確認できる？
「PostgreSQLで実行したSQL文(クエリ )の統計情報を取得したい場合、pg_stat_statementsの利用」を検討してみてください。
