2
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

PostgreSQLの統計情報コレクタ主要ビューまとめ

Posted at

統計情報コレクタとは

PostgreSQLの統計情報コレクタはサーバの活動状況に関する情報を収集し、報告するサブシステムです。
アクセス統計情報は稼働統計情報や実行時統計情報とも呼ばれ、サーバ全体の活動状況の統計が収集されます。
アクセス統計情報を参照するためには定義済みのビューや関数を利用します。

アクセス統計情報一覧

標準統計情報ビュー

pg_stat_activity

サーバプロセス単位にクエリの状態やプロセスの現在の活動情に関連した情報を表示します。
主に問題が発生しているクエリやプロセスを特定することを目的に利用します。

【SELECT結果サンプル】

postgres=# select pid, query_start, query, state from pg_stat_activity ;
 pid |          query_start          |                             query                             | state  
-----+-------------------------------+---------------------------------------------------------------+--------
  63 |                               |                                                               | 
  65 |                               |                                                               | 
  83 | 2020-12-12 02:45:10.656851+00 | select pid, query_start, query, state from pg_stat_activity ; | active
  61 |                               |                                                               | 
  60 |                               |                                                               | 
  62 |                               |                                                               | 
(6 rows)

pg_stat_database

データベース毎の統計情報を表示します。
各テーブルやインデックスの細かな利用状況の把握にはpg_stat_user_table等の別ビューを利用することになります。
また、blks_hit列とblks_read列の値からキャッシュヒット率を算出可能
キャッシュヒット率算出式: blks_hit/(blks_hit + blks_read)

【SELECT結果サンプル】

postgres=# select * from pg_stat_database;
 datid |  datname  | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | checksum_failures | checksum_last_failure | blk_read_time | blk_write_time |          stats_reset          
-------+-----------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+------------+-----------+-------------------+-----------------------+---------------+----------------+-------------------------------
     0 |           |           0 |           0 |             0 |        14 |      303 |          137 |          64 |            0 |           0 |           0 |         0 |          0 |          0 |         0 |                   |                       |             0 |              0 | 2020-12-12 02:40:13.486843+00
 13408 | postgres  |           1 |          52 |             2 |       239 |     3476 |        23272 |        1300 |           35 |           2 |           0 |         0 |          0 |          0 |         0 |                   |                       |             0 |              0 | 2020-12-12 02:40:13.486805+00
     1 | template1 |           0 |           0 |             0 |         0 |        0 |            0 |           0 |            0 |           0 |           0 |         0 |          0 |          0 |         0 |                   |                       |             0 |              0 | 
 13407 | template0 |           0 |           0 |             0 |         0 |        0 |            0 |           0 |            0 |           0 |           0 |         0 |          0 |          0 |         0 |                   |                       |             0 |              0 | 
(4 rows)

pg_stat_bgwriter

チェックポイントやバックグラウンドライタに関するデータベースクラスタ全体の統計情報を表示します。
パラメータチューニングの指標値として利用可能です。
checkpoints_reqの値が大きい場合はチェックポイントが頻発している可能性があるため、
checkpoint_segmentsの増加の検討が必要です。

【SELECT結果サンプル】

postgres=# select * from pg_stat_bgwriter ;
 checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_backend_fsync | buffers_alloc |          stats_reset          
-------------------+-----------------+-----------------------+----------------------+--------------------+---------------+------------------+-----------------+-----------------------+---------------+-------------------------------
                 4 |               0 |                  3401 |                    1 |                 32 |             0 |                0 |               2 |                     0 |           256 | 2020-12-12 02:39:55.072841+00
(1 row)

pg_stat_all_tables

テーブルあたり1行の形式で、テーブルへのアクセス統計情報を表示します。
テーブルスキャン1回分の読み取り行数、バキュームの対象量を確認可能です。
また、HOT更新の比率も確認可能です。
n_tup_hot_upd/N_tup_upd

pg_stat_all_tablesと似たビューとして、pg_stat_sys_tablespg_stat_user_tablsがありますが、これらのビューの違いは検索条件のみです。
pg_stat_sys_tablespg_stat_user_tablesの内容は全て
pg_stat_all_tablesに内包されます。

【SELECT結果サンプル】

postgres=# select * from pg_stat_all_tables ;
 relid |     schemaname     |         relname         | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count 
-------+--------------------+-------------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+-------------+-----------------+--------------+------------------+--------------+------------------+---------------+-------------------
  4171 | pg_toast           | pg_toast_1247           |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
  4151 | pg_toast           | pg_toast_1417           |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
 13253 | information_schema | sql_languages           |        0 |            0 |          |               |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
  3592 | pg_catalog         | pg_shseclabel           |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
 13270 | pg_toast           | pg_toast_13268          |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
  3603 | pg_catalog         | pg_ts_config_map        |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
  3079 | pg_catalog         | pg_extension            |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
  2830 | pg_toast           | pg_toast_2604           |        0 |            0 |        0 |             0 |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
 13243 | information_schema | sql_features            |        0 |            0 |          |               |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
   826 | pg_catalog         | pg_default_acl          |        0 |            0 |        2 |             0 |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |             |                 |              |                  |            0 |                0 |             0 |                 0
・・・

pg_statio_all_tables

テーブルあたり1行の形式でブロック単位のI/Oに関する統計情報を表示します。
基本的に*_blks_readはディスク、*_blks_hitはメモリ読み込みを意味するので、
*_blks_readが低いほど性能上は有利です。
ただし、*_blks_readはOSシステムキャッシュから読み込まれた場合であってもカウントアップされるので、
必ずしもディスクアクセス量をそのまま表示しているわけではないことに注意が必要です。

【SELECT結果サンプル】

postgres=# select * from pg_statio_all_tables;
 relid |     schemaname     |         relname         | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+--------------------+-------------------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
   826 | pg_catalog         | pg_default_acl          |              0 |             0 |             1 |            3 |               0 |              0 |              0 |             0
 13263 | information_schema | sql_parts               |              0 |             0 |               |              |               0 |              0 |              0 |             0
  3118 | pg_catalog         | pg_foreign_table        |              0 |             0 |             0 |            0 |               0 |              0 |              0 |             0
  3596 | pg_catalog         | pg_seclabel             |              0 |             0 |             0 |            0 |               0 |              0 |              0 |             0
  2603 | pg_catalog         | pg_amproc               |              3 |            66 |             3 |          175 |                 |                |                |              
  4165 | pg_toast           | pg_toast_3350           |              0 |             0 |             0 |            0 |                 |                |                |              
・・・

pg_stat_all_indexes

インデックス毎のアクセスに関する統計情報を表示します。
idx_tup_read列の値は該当のインデックスが利用された際に取得したエントリ数だけ加算されますが、
idx_tip_fetch列の値はBitmapIndexScanとして利用された場合には加算されません。

【SELECT結果サンプル】

postgres=# select * from pg_stat_all_indexes;
 relid | indexrelid | schemaname |         relname         |                 indexrelname                  | idx_scan | idx_tup_read | idx_tup_fetch 
-------+------------+------------+-------------------------+-----------------------------------------------+----------+--------------+---------------
 16387 |      16389 | pg_toast   | pg_toast_16384          | pg_toast_16384_index                          |        0 |            0 |             0
  4159 |       4160 | pg_toast   | pg_toast_2600           | pg_toast_2600_index                           |        0 |            0 |             0
  2830 |       2831 | pg_toast   | pg_toast_2604           | pg_toast_2604_index                           |        0 |            0 |             0
  4161 |       4162 | pg_toast   | pg_toast_3456           | pg_toast_3456_index                           |        0 |            0 |             0
  2832 |       2833 | pg_toast   | pg_toast_2606           | pg_toast_2606_index                           |        0 |            0 |             0
  4143 |       4144 | pg_toast   | pg_toast_826            | pg_toast_826_index                            |        0 |            0 |             0
  2834 |       2835 | pg_toast   | pg_toast_2609           | pg_toast_2609_index                           |        0 |            0 |             0
  4145 |       4146 | pg_toast   | pg_toast_3466           | pg_toast_3466_index                           |        0 |            0 |             0
  4147 |       4148 | pg_toast   | pg_toast_3079           | pg_toast_3079_index                           |        0 |            0 |             0
  4149 |       4150 | pg_toast   | pg_toast_2328           | pg_toast_2328_index                           |        0 |            0 |             0
  4151 |       4152 | pg_toast   | pg_toast_1417           | pg_toast_1417_index                           |        0 |            0 |             0
  4153 |       4154 | pg_toast   | pg_toast_3118           | pg_toast_3118_index                           |        0 |            0 |             0
  4155 |       4156 | pg_toast   | pg_toast_3394           | pg_toast_3394_index                           |        0 |            0 |             0
  4157 |       4158 | pg_toast   | pg_toast_2612           | pg_toast_2612_index                           |        0 |            0 |             0
  4163 |       4164 | pg_toast   | pg_toast_2615           | pg_toast_2615_index                           |        0 |            0 |             0
  4165 |       4166 | pg_toast   | pg_toast_3350           | pg_toast_3350_index                           |        0 |            0 |             0
  4167 |       4168 | pg_toast   | pg_toast_3256           | pg_toast_3256_index                           |        0 |            0 |             0
  2836 |       2837 | pg_toast   | pg_toast_1255           | pg_toast_1255_index                           |        0 |            0 |             0
  2838 |       2839 | pg_toast   | pg_toast_2618           | pg_toast_2618_index                           |        7 |           19 |            19
  3598 |       3599 | pg_toast   | pg_toast_3596           | pg_toast_3596_index                           |        0 |            0 |             0
  2840 |       2841 | pg_toast   | pg_toast_2619           | pg_toast_2619_index                           |        1 |            1 |             1
・・・

pg_statio_all_indexes

インデックス毎のI/Oに関する統計情報を表示します。
pg_statio_user_indexespg_statio_sys_indexesも同じ情報を保持します。

【SELECT結果サンプル】

postgres=# select * from pg_statio_all_indexes;
 relid | indexrelid | schemaname |         relname         |                 indexrelname                  | idx_blks_read | idx_blks_hit 
-------+------------+------------+-------------------------+-----------------------------------------------+---------------+--------------
  2600 |       2650 | pg_catalog | pg_aggregate            | pg_aggregate_fnoid_index                      |             2 |            1
  2601 |       2651 | pg_catalog | pg_am                   | pg_am_name_index                              |             0 |            0
  2601 |       2652 | pg_catalog | pg_am                   | pg_am_oid_index                               |             0 |            0
  2602 |       2653 | pg_catalog | pg_amop                 | pg_amop_fam_strat_index                       |             3 |           20
・・・

各標準統計ビューのクエリについて

各標準統計ビューが実際にどのようなクエリを発行しているかはpg_viewsdefinition列で確認できます。

【SELECT結果サンプル】

postgres=# select * from pg_views where viewname = 'pg_stat_user_tables';
 schemaname |      viewname       | viewowner |                                                                             definition                                                                             
------------+---------------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 pg_catalog | pg_stat_user_tables | postgres  |  SELECT pg_stat_all_tables.relid,                                                                                                                                 +
            |                     |           |     pg_stat_all_tables.schemaname,                                                                                                                                +
            |                     |           |     pg_stat_all_tables.relname,                                                                                                                                   +
            |                     |           |     pg_stat_all_tables.seq_scan,                                                                                                                                  +
            |                     |           |     pg_stat_all_tables.seq_tup_read,                                                                                                                              +
            |                     |           |     pg_stat_all_tables.idx_scan,                                                                                                                                  +
            |                     |           |     pg_stat_all_tables.idx_tup_fetch,                                                                                                                             +
            |                     |           |     pg_stat_all_tables.n_tup_ins,                                                                                                                                 +
            |                     |           |     pg_stat_all_tables.n_tup_upd,                                                                                                                                 +
            |                     |           |     pg_stat_all_tables.n_tup_del,                                                                                                                                 +
            |                     |           |     pg_stat_all_tables.n_tup_hot_upd,                                                                                                                             +
            |                     |           |     pg_stat_all_tables.n_live_tup,                                                                                                                                +
            |                     |           |     pg_stat_all_tables.n_dead_tup,                                                                                                                                +
            |                     |           |     pg_stat_all_tables.n_mod_since_analyze,                                                                                                                       +
            |                     |           |     pg_stat_all_tables.last_vacuum,                                                                                                                               +
            |                     |           |     pg_stat_all_tables.last_autovacuum,                                                                                                                           +
            |                     |           |     pg_stat_all_tables.last_analyze,                                                                                                                              +
            |                     |           |     pg_stat_all_tables.last_autoanalyze,                                                                                                                          +
            |                     |           |     pg_stat_all_tables.vacuum_count,                                                                                                                              +
            |                     |           |     pg_stat_all_tables.autovacuum_count,                                                                                                                          +
            |                     |           |     pg_stat_all_tables.analyze_count,                                                                                                                             +
            |                     |           |     pg_stat_all_tables.autoanalyze_count                                                                                                                          +
            |                     |           |    FROM pg_stat_all_tables                                                                                                                                        +
            |                     |           |   WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
(1 row)

参考

2
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
2
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?