F.52. pg_wait_sampling#
F.52. pg_wait_sampling #
F.52.1. About pg_wait_sampling #
pg_wait_sampling – sampling based statistics of
wait events
Version: 1.1.4
F.52.2. Introduction #
Tantor SE-1C provides an
information about current wait event of particular process.
However, in order to gather descriptive statistics of server
behavior user have to sample current wait event multiple times.
pg_wait_sampling is an extension for collecting
sampling statistics of wait events.
The module must be loaded by adding
pg_wait_sampling to
shared_preload_libraries in postgresql.conf,
because it requires additional shared memory and launches
background worker. This means that a server restart is needed to
add or remove the module.
When pg_wait_sampling is enabled, it collects
two kinds of statistics.
History of waits events. It’s implemented as in-memory ring buffer where samples of each process wait events are written with given (configurable) period. Therefore, for each running process user can see some number of recent samples depending on history size (configurable). Assuming there is a client who periodically read this history and dump it somewhere, user can have continuous history.
Waits profile. It’s implemented as in-memory hash table where count of samples are accumulated per each process and each wait event (and each query with
pg_stat_statements). This hash table can be reset by user request. Assuming there is a client who periodically dumps profile and resets it, user can have statistics of intensivity of wait events among time.
In combination with pg_stat_statements this
extension can also provide per query statistics.
pg_wait_sampling launches special background
worker for gathering the statistics above.
F.52.4. Usage #
pg_wait_sampling interacts with user by set of
views and functions.
pg_wait_sampling_current view – information
about current wait events for all processed including background
workers.
| Column name | Column type | Description |
|---|---|---|
| pid | int4 | Id of process |
| event_type | text | Name of wait event type |
| event | text | Name of wait event |
| queryid | int8 | Id of query |
pg_wait_sampling_get_current(pid int4) returns
the same table for single given process.
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 | text | Name of wait event |
| queryid | int8 | Id of query |
pg_wait_sampling_profile view – profile of wait
events obtained by sampling into in-memory hash table.
| Column name | Column type | Description |
|---|---|---|
| pid | int4 | Id of process |
| event_type | text | Name of wait event type |
| event | text | Name of wait event |
| queryid | int8 | Id of query |
| count | text | 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 | Description | Default value |
|---|---|---|---|
| pg_wait_sampling.history_size | int4 | Size of history in-memory ring buffer | 5000 |
| pg_wait_sampling.history_period | int4 | Period for history sampling in milliseconds | 10 |
| pg_wait_sampling.profile_period | int4 | Period for profile sampling in milliseconds | 10 |
| pg_wait_sampling.profile_pid | bool | Whether profile should be per pid | true |
| pg_wait_sampling.profile_queries | bool | Whether profile should be per query | true |
If pg_wait_sampling.profile_pid is set to
false, sampling profile wouldn’t be collected in per-process
manner. In this case the value of pid could would be always zero
and corresponding row contain samples among all the processes.
While pg_wait_sampling.profile_queries is set
to false queryid field in views will be zero.
These GUCs are allowed to be changed by superuser. Also, they are placed into shared memory. Thus, they could be changed from any backend and affects worker runtime.
F.52.5. Authors #
Alexander Korotkov
<[email protected]>, Postgres Professional, Moscow, Russia.Ildus Kurbangaliev
<[email protected]>, Postgres Professional, Moscow, Russia.