pg_wait_sampling is a Postgres Pro extension for
collecting sampling-based statistics on wait events.
Starting from the 9.6 version, Postgres Pro Standard provides information about
the current wait events for particular processes. However, to get
descriptive statistics of the server activity, you have to sample
wait events multiple times. The
pg_wait_sampling extension automates wait events sampling
by launching a special background worker. With pg_wait_sampling
enabled, you can get the following sampling-based data:
Waits history — the list of wait events for recent processes, with timestamps.
Waits profile — the number of wait event samples for all processes over time (per wait event type).
Current wait events for all processes, including background workers.
Using pg_wait_sampling,
you can troubleshoot dependencies for queries that process longer than expected.
You can see what a particular process
is waiting for at each moment of time, and analyze wait events statistics.
For the list of possible wait events, see
Table 26.4.
In combination with pg_stat_statements, this extension
can also provide per query statistics.
See Also
Viewing Statistics on Wait Events
The pg_wait_sampling extension is included into
Postgres Pro Standard and requires no special prerequisites.
To enable pg_wait_sampling, do the following:
Add pg_wait_sampling to the
shared_preload_libraries variable in the
postgresql.conf file:
shared_preload_libraries = 'pg_wait_sampling'
When using the pg_wait_sampling with the
pg_stat_statements extension, place
pg_stat_statements before pg_wait_sampling
in the shared_preload_libraries variable.
This ensures that the pg_stat_statements extension
does not overwrite query IDs that are used by pg_wait_sampling.
Create the pg_wait_sampling extension using the following query:
CREATE EXTENSION pg_wait_sampling;
Restart the server. Since pg_wait_sampling
requires additional shared memory and launches a background
worker, you must restart the server after adding or removing
pg_wait_sampling.
Once the extension is created, pg_wait_sampling starts
collecting statistics on wait events for each process. Optionally, you can
change the sampling frequency and configure statistics collection modes using
GUC variables.
If you want to stop collecting statistics, drop the
pg_wait_sampling extension, remove the
pg_wait_sampling from the
shared_preload_libraries variable, and restart
the server again.
When pg_wait_sampling is enabled, wait events
are sampled each 10ms by default. You can access the collected statistics
through a set of functions and views. If some of your queries
appear stalled or take longer than expected, you can review all
the current wait events in the
pg_wait_sampling_current view:
SELECT * FROM pg_wait_sampling_current;
The returned statistics covers all the active processes,
including background workers. To filter the view for a single
process, run pg_wait_sampling_get_current() function
specifying the process ID:
SELECT * FROM pg_wait_sampling_get_current(pid);
To better understand the dynamics of the recent wait events, you
can access the pg_wait_sampling_history view
that provides the wait events history for the latest processes:
SELECT * FROM pg_wait_sampling_history;
The returned view displays wait samples per process, with event timestamps.
Waits history is implemented as an in-memory ring buffer. By
default, the history size is limited to 5000 samples. To save history for
a longer period of time, you can
modify the pg_wait_sampling.history_size
variable, or connect a client application to periodically read
waits history and dump it to a local storage.
To monitor wait events in your database over time, use the
pg_wait_sampling_profile view:
SELECT * FROM pg_wait_sampling_profile;
Waits profile is stored as an in-memory hash table that
accumulates samples per each process and each wait event. You
can reset waits profile by calling the
pg_wait_sampling_reset_profile() function. If
you connect a client to your database that periodically dumps
the wait events data and resets the profile, you can save and
compare statistics of wait events intensity over time.
Since wait sampling statistics is stored in RAM, make sure to reset the waits profile from time to time to avoid memory overflow.
If you are not interested in the distribution of wait events
between processes, you can set the
pg_wait_sampling.profile_pid variable to
false. In this case, the pid value
for each process is set to zero, and wait samples for all the processes
are stored together.
The pg_wait_sampling extension is configured
with GUC variables. You must
have superuser rights to change GUC variables. Since these
variables are placed into shared memory, you can change them
from any backend that affects the
pg_wait_sampling background worker runtime.
pg_wait_sampling.history_size (int4)
Size of in-memory ring buffer for history sampling, in the number of samples.
Default: 5000
pg_wait_sampling.history_period (int4)
Time interval for history sampling, in milliseconds.
Default: 10
pg_wait_sampling.profile_period (int4)
Time interval for waits profile sampling, in milliseconds.
Default: 10
pg_wait_sampling.profile_pid (bool)
Sampling mode for waits profile. If set to true,
waits profile is collected per process. If you set
pg_wait_sampling.profile_pid to
false, waits profile is collected for all
processes together. In this case, the
pid value for each process is set to
zero, and the corresponding row contains wait samples for all the processes.
Default: true
pg_wait_sampling.profile_queries (enum)
Sampling mode for the waits profile. If pg_wait_sampling.profile_queries
is set to none, the queryid
field in views will be zero. If it is set to top,
query IDs of only the top level statements are recorded. If it is set
to all, query IDs of nested statements are
recorded. To collect the waits profile per query, ensure that the
pg_stat_statements extension is configured
and set pg_wait_sampling.profile_queries to
top. For version 14 or higher, you can compute query IDs by
configuring the compute_query_id parameter and set the
pg_wait_sampling.profile_queries value to top or
all.
For details, see Section 18.9.2.
Default: top
pg_wait_sampling.sample_cpu (bool)
The sampling mode that determines whether to perform sampling of on-CPU
backends. If pg_wait_sampling.sample_cpu is set to
true, then sampling also includes processes that are
not waiting for anything. The wait event columns for such processes will
have a NULL value.
Default: true
The pg_wait_sampling_current view provides
the information about the current wait events for all
processes, including background workers.
Table F.32. pg_wait_sampling_current View
| Column Name | Column Type | Description |
|---|---|---|
pid
|
int4
| Process ID |
event_type
|
text
| Name of wait event type |
event
|
text
| Name of wait event |
queryid
|
int8
| Query ID |
The pg_wait_sampling_history view provides
the history of wait events. This data is stored as an
in-memory ring buffer.
Table F.33. pg_wait_sampling_history View
| Column Name | Column Type | Description |
|---|---|---|
pid
|
int4
| Process ID |
ts
|
timestamptz
| Sample timestamp |
event_type
|
text
| Name of wait event type |
event
|
text
| Name of wait event |
queryid
|
int8
| Query ID |
The pg_wait_sampling_profile view provides
the profile of wait events. This data is stored as an
in-memory hash table.
Table F.34. pg_wait_sampling_profile View
| Column Name | Column Type | Description |
|---|---|---|
pid
|
int4
| Process ID |
event_type
|
text
| Name of wait event type |
event
|
text
| Name of wait event |
queryid
|
int8
| Query ID |
count
|
int8
| Number of samples |
pg_wait_sampling_get_current(pid int4)
Returns the pg_wait_sampling_current view
with the list of current wait events. If you set the pid
argument, the view is filtered for the process with this pid.
Arguments:
pid — Optional. The process ID
for which to display the current wait events.
pg_wait_sampling_reset_profile()
Resets the waits profile and clears the memory.
Alexander Korotkov
Ildus Kurbangaliev <i.kurbangaliev@gmail.com>