PostgreSQL 9.6 等待事件出爐

digoal

2016-10-06

postgresql , 9.6 , 等待事件 , wait_event

postgresql 9.6動态視圖pg_stat_activity新增了wait_event_type, wait_event的等待事件展示。

當會話處于等待狀态時，wait_event與wait_event_type非空，表示會話正在等待的類型。

根據等待資訊，可以了解目前會話的狀态。

将來也可以通過插件的形式，掌握資料庫在某個時間段内的等待事件統計，更好的診斷資料庫的健康狀态。

已有的插件如下

<a href="https://github.com/postgrespro/pg_wait_sampling">https://github.com/postgrespro/pg_wait_sampling</a>

用于對等待事件進行采樣。

例子

pg_wait_sampling_history view – history of wait events obtained by sampling into

in-memory ring buffer.

column name

column type

description

pid

int4

id of process

ts

timestamptz

sample timestamp

event_type

text

name of wait event type

event

name of wait event

pg_wait_sampling_profile view – profile of wait events obtained by sampling into

in-memory hash table.

count

count of samples

pg_wait_sampling_reset_profile() function resets the profile.

the work of wait event statistics collector worker is controlled by following

gucs.

parameter name

data type

default value

pg_wait_sampling.history_size

size of history in-memory ring buffer

5000

pg_wait_sampling.history_period

period for history sampling in milliseconds

10

pg_wait_sampling.profile_period

period for profile sampling in milliseconds

pg_wait_sampling.profile_pid

bool

whether profile should be per pid

true

詳見

<a href="https://www.postgresql.org/docs/9.6/static/monitoring-stats.html">https://www.postgresql.org/docs/9.6/static/monitoring-stats.html</a>

pg_stat_activity 視圖新增等待事件列，可以觀察到會話目前的等待。

1. wait_event_type

表示等待時間的類别，如果backend處于等待狀态則有内容，否則為空。

類别如下

1.1 lwlocknamed:

1.2 lwlocktranche:

1.3 lock:

1.4 bufferpin:

2. wait_event

2.1 lwlocknamed

2.2 lwlocktranche

2.3 lock

2.4 bufferpin

3. 擷取當指定pid目前的等待資訊。

<a href="http://info.flagcounter.com/h9v1">count</a>