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
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.
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.4.tar.gz -O pg_wait_sampling-v1.1.4.tar.gz tar zxvf pg_wait_sampling-v1.1.4.tar.gz cd pg_wait_sampling-v1.1.4
Then, compile the extension:
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:
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:
10. Period for profile sampling in milliseconds.
true. Whether profile should be per pid. Should be set to false for PoWA usage. true will work, but consume resources needlessly.
true. Whether profile should be per normalized query, as provided by pg_stat_statements extension. Should be set to true for PoWA usage.