## F.53. pg_wait_sampling

`pg_wait_sampling` is a Postgres Pro Enterprise extension for collecting sampling-based statistics on wait events.

Starting from the 9.6 version, Postgres Pro Enterprise 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 take 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 28.4.

See Also

Viewing Statistics on Wait Events

Reference

### F.53.1. Installation

The `pg_wait_sampling` extension is included into Postgres Pro Enterprise and requires no special prerequisites.

To enable `pg_wait_sampling`, do the following:

1. Add `pg_wait_sampling` to the `shared_preload_libraries` variable in the `postgresql.conf` file:

       shared_preload_libraries = 'pg_wait_sampling'

2. Create the `pg_wait_sampling` extension using the following query:

       CREATE EXTENSION pg_wait_sampling;

3. 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.

### F.53.2. Usage

#### F.53.2.1. Viewing Statistics on Wait Events

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_get_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_get_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.

### Important

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.

### F.53.3. Reference

#### F.53.3.1. GUC Variables

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` (`int4`)

  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`

#### F.53.3.2. pg_wait_sampling Views

##### F.53.3.2.1. pg_wait_sampling_current View

The `pg_wait_sampling_current` view provides the information about the current wait events for all processes, including background workers.

Table F.41. 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

##### F.53.3.2.2. pg_wait_sampling_history View

The `pg_wait_sampling_history` view provides the history of wait events. This data is stored as an in-memory ring buffer.

Table F.42. 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

##### F.53.3.2.3. pg_wait_sampling_profile View

The `pg_wait_sampling_profile` view provides the profile of wait events. This data is stored as an in-memory hash table.

Table F.43. 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
`count` | `text` | Number of samples

#### F.53.3.3. Functions

`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.

### F.53.4. Authors

Alexander Korotkov `<a.korotkov@postgrespro.ru>`, Postgres Professional, Moscow, Russia