pg_stat_activityの変更

はじめに

にゃーん
この記事は、PostgreSQL 10全部ぬこ Advent Calendar 2017 の15日目のエントリです。

今日のお題

昨日書いた(システムカタログの差分)ではシステムカタログ全般の変更点を挙げたけど、今日はpg_stat_activityに焦点をあてて変更点を探ってみたい。

pg_stat_activityとはなんぞ

pg_stat_activityは稼働統計情報ビューというPostgreSQLの運用状態をSQLで参照するためのビューの一つ。
pg_stat_activityはPostgreSQLのプロセスの活動状況をを報告するもので、これまでは主にPostgreSQLサーバへの接続しているセッション数や、各セッションの大まかな状態を参照できるビューである。

pg_stat_activityの何が変わったのか

PostgreSQL 10ではpg_stat_activityに関して3つの変更が入っている。

• 表示される列の情報が増えた
• 表示対象のプロセスが増えた
• wait_eventの内容が詳細化された

以下、それぞれについて見ていく。

表示される列の情報が増えた

PostgreSQL 10からは、backend_typeという列が追加された。

PostgreSQL 9.6まではこんな感じ。

postgres=# \d pg_stat_activity
           View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Modifiers 
------------------+--------------------------+-----------
 datid            | oid                      | 
 datname          | name                     | 
 pid              | integer                  | 
 usesysid         | oid                      | 
 usename          | name                     | 
 application_name | text                     | 
 client_addr      | inet                     | 
 client_hostname  | text                     | 
 client_port      | integer                  | 
 backend_start    | timestamp with time zone | 
 xact_start       | timestamp with time zone | 
 query_start      | timestamp with time zone | 
 state_change     | timestamp with time zone | 
 wait_event_type  | text                     | 
 wait_event       | text                     | 
 state            | text                     | 
 backend_xid      | xid                      | 
 backend_xmin     | xid                      | 
 query            | text                     | 

PostgreSQL 10ではこんな感じ。
(\dの表示列が増えているのはPostgreSQL 10の別の改善によるもの)

postgres=# \d pg_stat_activity
                      View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Collation | Nullable | Default 
------------------+--------------------------+-----------+----------+---------
 datid            | oid                      |           |          | 
 datname          | name                     |           |          | 
 pid              | integer                  |           |          | 
 usesysid         | oid                      |           |          | 
 usename          | name                     |           |          | 
 application_name | text                     |           |          | 
 client_addr      | inet                     |           |          | 
 client_hostname  | text                     |           |          | 
 client_port      | integer                  |           |          | 
 backend_start    | timestamp with time zone |           |          | 
 xact_start       | timestamp with time zone |           |          | 
 query_start      | timestamp with time zone |           |          | 
 state_change     | timestamp with time zone |           |          | 
 wait_event_type  | text                     |           |          | 
 wait_event       | text                     |           |          | 
 state            | text                     |           |          | 
 backend_xid      | xid                      |           |          | 
 backend_xmin     | xid                      |           |          | 
 query            | text                     |           |          | 
 backend_type     | text                     |           |          | 

このbackend_typeは次項の表示対象プロセスの増加にも関連している。

表示対象のプロセスが増えた

PostgreSQL 10からは、クライアントから接続されたときに生成されるバックエンドプロセス以外の、PostgreSQLのバックグラウンドプロセスも表示されるようになった。

postgres=# SELECT pid, datname, usename, state FROM pg_stat_activity;
  pid  | datname  | usename  | state  
-------+----------+----------+--------
 41022 | postgres | user_a   | idle
 40340 | postgres | postgres | active
(2 rows)

PostgreSQL 10で追加されたbackend_typeを追加して同様にpg_activityを検索してみる。

postgres=# SELECT pid, datname, usename, state, backend_type FROM pg_stat_activity;
  pid  | datname  | usename  | state  |    backend_type     
-------+----------+----------+--------+---------------------
 34433 |          |          |        | autovacuum launcher
 34435 |          | postgres |        | background worker
 41986 | postgres | user_a   | idle   | client backend
 42067 | postgres | postgres | active | client backend
 34431 |          |          |        | background writer
 34430 |          |          |        | checkpointer
 34432 |          |          |        | walwriter
(7 rows)

PostgreSQL 9.6までは、pg_stat_activityで表示されるプロセスは、backend_typeはclient backendのプロセスのみだった。
他に表示されているプロセスは、PostgreSQLの動作を裏で支えるバックグラウンドプロセスだ。

Linuxのpsコマンドで見たときに表示されているプロセス名と比較すると

[nuko@localhost src]$ ps aux | grep postgres
nuko     34427  0.0  0.2 281584 13436 pts/1    S    12月13   0:42 /home/nuko/pgsql/pgsql-10/bin/postgres -D /home/nuko/pgdata/10
nuko     34428  0.0  0.0 136540  1096 ?        Ss   12月13   0:00 postgres: logger process   
nuko     34430  0.0  0.9 281896 44328 ?        Ss   12月13   0:00 postgres: checkpointer process   
nuko     34431  0.0  0.0 281584  2492 ?        Ss   12月13   0:00 postgres: writer process   
nuko     34432  0.0  0.1 281584  5388 ?        Ss   12月13   0:01 postgres: wal writer process   
nuko     34433  0.0  0.0 282300  2428 ?        Ss   12月13   0:09 postgres: autovacuum launcher process   
nuko     34434  0.0  0.0 137560  1912 ?        Ss   12月13   0:26 postgres: stats collector process   
nuko     34435  0.0  0.0 281872  1628 ?        Ss   12月13   0:00 postgres: bgworker: logical replication launcher   
nuko     49845  0.0  0.0 124812  1792 pts/8    S+   08:51   0:00 psql -U postgres test
nuko     49846  0.0  0.0 282380  3032 ?        Ss   08:51   0:00 postgres: postgres test [local] idle
nuko     49886  0.0  0.0 112660   996 pts/7    R+   08:51   0:00 grep --color=auto postgres

で表示されているプロセス名が表示されているのが分かる。
で、ここで気づいたのだが、そういえばpsの結果にはbgworker: logical replication launcherが表示されているプロセス(pid=34435)は、pg_stat_activityでは、単にbackground workerと表示されている。おそらく自作のバックグラウンドワーカープロセスも同じように表示されるのであろう。
backend_typeで示されるプロセス種別の一覧はPostgreSQL文書のpg_stat_activityの説明にもきちんと書いてあるので、ここでは割愛。

今までPostgreSQLへの接続数を、

SELECT COUNT(*) FROM pg_stat_activity

のクエリで取得して監視していた場合には、

SELECT COUNT(*) FROM pg_stat_activity
  WHERE backend_type = 'client backend'

のようなクエリに変更する必要がある。

パラレルクエリ時はどう表示されるのか

PostgreSQL 9.6からは特定ケースのクエリについては、そのクエリ内でワーカープロセスを起動して、元のバックエンドプロセスとワーカープロセス間で並列に処理を実行する、パラレルクエリ機能がサポートされている。
では、パラレルクエリ処理中のときには、pg_stat_activityやpsコマンドではどう表示されるのか、ちょっと試してみた。

例えばpgbenchで(scale=1)初期化したデータベースに対して、以下のようなクエリの実行計画を確認してみる。
（事前にSET paralell_setup_cost = 1とかにして、パラレルクエリが動きやすくしておく）

bench=# EXPLAIN SELECT COUNT(pg_sleep(1)), bid FROM pgbench_accounts GROUP BY bid;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=2670.54..2670.56 rows=1 width=12)
   Group Key: bid
   ->  Sort  (cost=2670.54..2670.54 rows=1 width=12)
         Sort Key: bid
         ->  Gather  (cost=2670.42..2670.53 rows=1 width=12)
               Workers Planned: 1
               ->  Partial HashAggregate  (cost=2669.42..2669.43 rows=1 width=12)
                     Group Key: bid
                     ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..2228.24 rows=58824 width=4)
(9 rows)

今度は実際に上記のSELECT文をEXPLAIN ANALYZE背景で動作させてみる。

bench=# EXPLAIN ANALYZE SELECT COUNT(pg_sleep(1)), bid FROM pgbench_accounts GROUP BY bid;

このクエリが実行し終えるまでには、10万秒以上かかるので、ゆっくり状態を確認してみようｗ

bench=# SELECT pid, state, backend_type, query FROM pg_stat_activity;
  pid  | state  |    backend_type     |                                       query                                        
-------+--------+---------------------+------------------------------------------------------------------------------------
 52378 |        | autovacuum launcher | 
 52380 |        | background worker   | 
  5619 | active | client backend      | SELECT pid, state, backend_type, query FROM pg_stat_activity;
  6959 | active | client backend      | EXPLAIN ANALYZE SELECT COUNT(pg_sleep(1)), bid FROM pgbench_accounts GROUP BY bid;
  8499 | active | background worker   | EXPLAIN ANALYZE SELECT COUNT(pg_sleep(1)), bid FROM pgbench_accounts GROUP BY bid;
 52376 |        | background writer   | 
 52375 |        | checkpointer        | 
 52377 |        | walwriter           | 
(8 rows)

出力結果のquery列に注目。さきほどのEXPLAINクエリが2行表示されているのが分かると思う。

• 1つの行(pid=6959)はbackend_typeがclient backendになっている。
• もう1つの行(pid=8499)はbackend_typeがbackground workerになっている。これはパラレルクエリのためにクエリ実行中に起動された、バックグラウンドワーカのプロセスだ。

このように、パラレルクエリ背景で起動されたプロセスも、pg_stat_activityには表示されることが分かる。

wait_eventの内容が詳細化された

PostgreSQL 9.5まで、セッションの排他待ち状態は、単にwaitingという列に出力されていただけだったが、PostgreSQL 9.6からは、waitingの代わりにwait_event_typeとwait_eventという列に分離され、排他状態に関する情報がより詳細に表示されるようになった。
PostgreSQL 10ではwait_event_typeで表示される状態が増加さらに増加した。また、PostgreSQL 9.6には存在した状態が、PostgreSQL 10では廃止になったものもあるので、なかなかややこしい。

PostgreSQL 9.6	PostgreSQL 10
LWLockNamed	N/A
LWLockTranche	N/A
N/A	LWLock
Lock	Lock
BufferPin	BufferPin
N/A	Activity
N/A	Client
N/A	Extension
N/A	IPC
N/A	Timeout
N/A	IO

ここでは詳細は書かないが、さらに、wait_event_typeよりも詳細な情報を示すwait_eventもある。もちろん、PostgreQSQL 10でいろいろ種別が増えている。
wait_event_type列やwait_event列の詳細(どういうときに、どの内容が表示されるのかは、別途調査しないといけないな・・・。

おわりに

何気にpg_stat_activityへの変更が入っているので、今までpg_stat_activityを参照して接続数を監視を行っているシステムや、wait_event_type列やwait_event列を参照しているシステムは、PostgreSQL 10へのアップグレード時に、監視スクリプト(監視用SQL)を見直して修正が必要になりそうだ。

参考：該当するリリースノート

本エントリに関連するPostgreSQL 10リリースノートの記載です。

E.2.3.4. Utility Commands

• Add pg_stat_activity reporting of low-level wait states (Michael Paquier, Robert Haas, Rushabh Lathia)
• Show auxiliary processes, background workers, and walsender processes in pg_stat_activity (Kuntal Ghosh, Michael Paquier)
• Allow pg_stat_activity to show the SQL query being executed by parallel workers (Rafia Sabih)
• Rename pg_stat_activity.wait_event_type values LWLockTranche and LWLockNamed to LWLock (Robert Haas)