ablog

不器用で落着きのない技術者のメモ

Aurora PostgreSQL で pg_stat_statements ビューを参照できるようにする

AWS PostgreSQL

Aurora PostgreSQL で pg_stat_statements ビューを参照できるようにする手順。

設定手順

  • RDS でパラメータグループを作成
    • パラメータグループファミリー: aurora-postgresql11
    • タイプ: DB Parameter Group
  • 作成したパラメータグループで以下の通り設定
パラメータ 設定した値 説明
pg_stat_statements.max 10000 pg_stat_statements.maxは、このモジュールによって記録されるSQL文の最大数(すなわち、pg_stat_statementsビューにおける行の最大数)です。これを超えて異なるSQL文を検出した場合は、最も実行回数の低いSQL文の情報が捨てられます。 デフォルトは5000です。 このパラメータはサーバの起動時にのみ指定できます。
pg_stat_statements.save 1 pg_stat_statements.saveは、サーバを終了させる際に文の統計情報を保存するかどうかを指定します。 offの場合、統計情報は終了時に保存されず、サーバ開始時に再読み込みもされません。 デフォルト値はonです。 このパラメータはpostgresql.confファイル、またはサーバコマンドラインでのみ設定できます。
pg_stat_statements.track TOP pg_stat_statements.trackは、どのSQL文をモジュールによって計測するかを制御します。 topを指定した場合は（直接クライアントによって発行された）最上層のSQL文を記録します。 allは（関数の中から呼び出された文などの）入れ子になった文も記録します。 noneは文に関する統計情報収集を無効にします。 デフォルトはtopです。 この設定はスーパーユーザだけが変更できます。
pg_stat_statements.track_utility 設定せず pg_stat_statements.track_utilityは、このモジュールがユーティリティコマンドを記録するかどうかを指定します。 ユーティリティコマンドとは、 SELECT、INSERT、UPDATEおよびDELETE以外のすべてです。 デフォルトはonです。 この設定はスーパーユーザのみが変更できます。
  • pg_stat_statements をインストール
# installed_version が空なので有効化されていない
testdb=> select * from pg_available_extensions where name = 'pg_stat_statements';
        name        | default_version | installed_version |                          comment
--------------------+-----------------+-------------------+-----------------------------------------------------------
 pg_stat_statements | 1.6             |                   | track execution statistics of all SQL statements executed
(1 行)

# インストールする
testdb=> CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

# installed_version にバージョンが表示される（インストールされている） 
testdb=> select * from pg_available_extensions where name = 'pg_stat_statements';
        name        | default_version | installed_version |                          comment
--------------------+-----------------+-------------------+-----------------------------------------------------------
 pg_stat_statements | 1.6             | 1.6

使ってみる

  • pg_stat_statements に問合せが可能になっている。
testdb=> select substr(query, 0, 60) as query,calls,total_time FROM pg_stat_statements
testdb->  ORDER BY total_time DESC LIMIT 10;
                            query                            |  calls  |    total_time
-------------------------------------------------------------+---------+------------------
 UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE  |  911811 | 563141.325559004
 select sum(numbackends) numbackends, sum(xact_commit) xact_ |   38337 | 371650.687600002
 select sum(blks_read), sum(blks_hit) from pg_stat_database  |   38336 | 353140.195751002
 SELECT abalance FROM pgbench_accounts WHERE aid = $1        | 5154690 | 341899.975895946
 WITH upsert AS (UPDATE public.rds_heartbeat2 SET value=$1 W | 3430600 | 172379.863405007
 UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE t |  911817 | 121658.844748003
 SELECT coalesce(sum(used_bytes),$1) FROM aurora_stat_file() |   76674 |    113082.077462
 select count(distinct transactionid::varchar) active_transa |   38337 | 60694.9610260003
 select count(distinct pid) blocked_transactions from pg_loc |   38337 | 59171.8662820004
 select queryid, calls, total_time, rows, shared_blks_hit, s |   17156 | 54421.0345979998
(10 行)

環境

参考