pg_wait_sampling

The pg_wait_sampling extension is devlopped by PostgresProfessional. It samples wait_events of all SQL queries executed on a given PostgreSQL server, providing waits profile, an accumulated view of wait events.

The waits profile is available in view called pg_wait_sampling_profile. This view contains one row for each distinct Process ID, wait event type, event and query ID.

Where is it used in powa-web ?

If the extension is available, you should see a “Wait events for all databases” table on the overview page and a “Wait events for all queries” table on the database page. Those tables report the list of reported wait events for the given period, either on the overall instance or on the database only.

On the query page, a “Wait Events” tab is available, where you’ll see both a graph of reported wait events, per type, and a table of all reported wait events, both for the given period.

Installation

As seen in Quickstart, the PostgreSQL development packages should be available.

First, download and extract the latest release of pg_wait_sampling:

wget https://github.com/postgrespro/pg_wait_sampling/archive/v1.1.tar.gz -O pg_wait_sampling-v1.1.tar.gz
tar zxvf pg_wait_sampling-v1.1.tar.gz
cd pg_wait_sampling-v1.1

Then, compile the extension:

make USE_PGXS=1

Then install the compiled package:

make USE_PGXS=1 install

Then you just have to declare the extension in the postgresql.conf file, like this :

shared_preload_libraries = 'pg_stat_statements,pg_wait_sampling'

Restart the PostgreSQL server to reload the libraries.

Connect to the server as a superuser and type:

CREATE EXTENSION pg_wait_sampling;

Using with PoWA

If you want PoWA to handle this extension, you have to connect as a superuser on the database where you installed PoWA, and type:

SELECT powa_wait_sampling_register();

Configuration

For a complete description of the confirugration parameters, please refer to the official pg_wait_sampling documentation.

For PoWA needs, here are the important settings:

pg_wait_sampling.profile_period:
Defaults to 10. Period for profile sampling in milliseconds.
pg_wait_sampling.profile_pid:
Defaults to true. Whether profile should be per pid. Should be set to true for PoWA usage.
pg_wait_sampling.profile_queries:
Defaults to false. Whether profile should be per normalized query, as provided by pg_stat_statements extension. Should be set to true for PoWA usage.