aurora_stat_backend_wait
显示特定后端进程等待活动的统计数据。
语法
aurora_stat_backend_waits(pid)Arguments
pid – 后端进程的 ID。您可以通过使用 pg_stat_activity 视图获取进程 ID。
返回类型
包含以下列的 SETOF 记录：
type_id– 表示等待事件类型的数字，例如1表示轻量级锁（LWLock），3表示锁，或者6表示客户端会话。当您将此函数的结果与aurora_stat_wait_type函数中的列连接时，这些值变得有意义，如示例中所示。event_id– 等待事件的识别号码。将此值与来自aurora_stat_wait_event的列连接以获取有意义的事件名称。waits– 指定进程 ID 累积的等待次数的计数。wait_time– 以毫秒为单位的等待时间。
使用说明
您可以使用此函数分析自连接打开以来发生的特定后端（会话）等待事件。要获取更多有关等待事件名称和类型的有意义信息，您可以通过使用 JOIN 组合此函数 aurora_stat_wait_type 和 aurora_stat_wait_event，如示例所示。
示例
此示例显示了后端进程 ID 3027 的所有等待、类型和事件名称。
=>SELECT type_name, event_name, waits, wait_time FROM aurora_stat_backend_waits(3027) NATURAL JOIN aurora_stat_wait_type() NATURAL JOIN aurora_stat_wait_event();type_name | event_name | waits | wait_time -----------+------------------------+-------+------------ LWLock | ProcArrayLock | 3 | 27 LWLock | wal_insert | 423 | 16336 LWLock | buffer_content | 11840 | 1033634 LWLock | lock_manager | 23821 | 5664506 Lock | tuple | 10258 | 152280165 Lock | transactionid | 78340 | 1239808783 Client | ClientRead | 34072 | 17616684 IO | ControlFileSyncUpdate | 2 | 0 IO | ControlFileWriteUpdate | 4 | 32 IO | RelationMapRead | 2 | 795 IO | WALWrite | 36666 | 98623 IO | XactSync | 4867 | 7331963
此示例显示了所有活动会话的当前和累积等待类型及等待事件（pg_stat_activity state <> 'idle'）[但不包括调用此函数的当前会话（pid <> pg_backend_pid()）]。
=>SELECT a.pid, a.usename, a.app_name, a.current_wait_type, a.current_wait_event, a.current_state, wt.type_name AS wait_type, we.event_name AS wait_event, a.waits, a.wait_time FROM (SELECT pid, usename, left(application_name,16) AS app_name, coalesce(wait_event_type,'CPU') AS current_wait_type, coalesce(wait_event,'CPU') AS current_wait_event, state AS current_state, (aurora_stat_backend_waits(pid)).* FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND state <> 'idle') a NATURAL JOIN aurora_stat_wait_type() wt NATURAL JOIN aurora_stat_wait_event() we;pid | usename | app_name | current_wait_type | current_wait_event | current_state | wait_type | wait_event | waits | wait_time -------+----------+----------+-------------------+--------------------+---------------+-----------+------------------------+-------+----------- 30099 | postgres | pgbench | Lock | transactionid | active | LWLock | wal_insert | 1937 | 29975 30099 | postgres | pgbench | Lock | transactionid | active | LWLock | buffer_content | 22903 | 760498 30099 | postgres | pgbench | Lock | transactionid | active | LWLock | lock_manager | 10012 | 223207 30099 | postgres | pgbench | Lock | transactionid | active | Lock | tuple | 20315 | 63081529 . . . 30099 | postgres | pgbench | Lock | transactionid | active | IO | WALWrite | 93293 | 237440 30099 | postgres | pgbench | Lock | transactionid | active | IO | XactSync | 13010 | 19525143 30100 | postgres | pgbench | Lock | transactionid | active | LWLock | ProcArrayLock | 6 | 53 30100 | postgres | pgbench | Lock | transactionid | active | LWLock | wal_insert | 1913 | 25450 30100 | postgres | pgbench | Lock | transactionid | active | LWLock | buffer_content | 22874 | 778005 . . . 30109 | postgres | pgbench | IO | XactSync | active | LWLock | ProcArrayLock | 3 | 71 30109 | postgres | pgbench | IO | XactSync | active | LWLock | wal_insert | 1940 | 27741 30109 | postgres | pgbench | IO | XactSync | active | LWLock | buffer_content | 22962 | 776352 30109 | postgres | pgbench | IO | XactSync | active | LWLock | lock_manager | 9879 | 218826 30109 | postgres | pgbench | IO | XactSync | active | Lock | tuple | 20401 | 63581306 30109 | postgres | pgbench | IO | XactSync | active | Lock | transactionid | 50769 | 211645008 30109 | postgres | pgbench | IO | XactSync | active | Client | ClientRead | 89901 | 44192439
此示例显示了所有活动会话的当前和前三（3）个累积等待类型和等待事件（pg_stat_activity state <> 'idle'），不包括当前会话（pid <>pg_backend_pid()）。
=>SELECT top3.* FROM (SELECT a.pid, a.usename, a.app_name, a.current_wait_type, a.current_wait_event, a.current_state, wt.type_name AS wait_type, we.event_name AS wait_event, a.waits, a.wait_time, RANK() OVER (PARTITION BY pid ORDER BY a.wait_time DESC) FROM (SELECT pid, usename, left(application_name,16) AS app_name, coalesce(wait_event_type,'CPU') AS current_wait_type, coalesce(wait_event,'CPU') AS current_wait_event, state AS current_state, (aurora_stat_backend_waits(pid)).* FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND state <> 'idle') a NATURAL JOIN aurora_stat_wait_type() wt NATURAL JOIN aurora_stat_wait_event() we) top3 WHERE RANK <=3;pid | usename | app_name | current_wait_type | current_wait_event | current_state | wait_type | wait_event | waits | wait_time | rank -------+----------+----------+-------------------+--------------------+---------------+-----------+-----------------+---------+------------+------ 20567 | postgres | psql | CPU | CPU | active | LWLock | wal_insert | 25000 | 67512003 | 1 20567 | postgres | psql | CPU | CPU | active | IO | WALWrite | 3071758 | 1016961 | 2 20567 | postgres | psql | CPU | CPU | active | IO | BufFileWrite | 20750 | 184559 | 3 27743 | postgres | pgbench | Lock | transactionid | active | Lock | transactionid | 237350 | 1265580011 | 1 27743 | postgres | pgbench | Lock | transactionid | active | Lock | tuple | 93641 | 341472318 | 2 27743 | postgres | pgbench | Lock | transactionid | active | Client | ClientRead | 417556 | 204796837 | 3 . . . 27745 | postgres | pgbench | IO | XactSync | active | Lock | transactionid | 238068 | 1265816822 | 1 27745 | postgres | pgbench | IO | XactSync | active | Lock | tuple | 93210 | 338312247 | 2 27745 | postgres | pgbench | IO | XactSync | active | Client | ClientRead | 419157 | 207836533 | 3 27746 | postgres | pgbench | Lock | transactionid | active | Lock | transactionid | 237621 | 1264528811 | 1 27746 | postgres | pgbench | Lock | transactionid | active | Lock | tuple | 93563 | 339799310 | 2 27746 | postgres | pgbench | Lock | transactionid | active | Client | ClientRead | 417304 | 208372727 | 3
