本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。
aurora_stat_backend_waits
顯示特定後端程序的等待活動統計資料。
語法
aurora_stat_backend_waits(pid)引數
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
此範例會示範所有作用中工作階段 (pg_stat_activity state <> 'idle') 目前及前三 (3) 個累積等待類型和等待事件，目前工作階段 (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
