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>