Repository files navigation

PostgreSQL provides information about current wait event of particularprocess. However, in order to gather descriptive statistics of serverbehavior users have to sample current wait events multiple times.pg_wait_sampling is an extension for collecting sampling statistics of waitevents.

The module must be loaded by addingpg_wait_sampling toshared_preload_libraries in postgresql.conf, because it requires additionalshared memory and launches a background worker. This means that a server restartis needed to add or remove the module.

When used withpg_stat_statements it is recommended to putpg_stat_statementsbeforepg_wait_sampling inshared_preload_libraries so queryIds ofutility statements are not rewritten by the former.

Whenpg_wait_sampling is enabled, it collects two kinds of statistics.

• History of waits events. It's implemented as in-memory ring buffer wheresamples of each process' wait events are written with given (configurable)period. Therefore, for each running process user can see some number ofrecent samples depending on history size (configurable). Assuming there isa client who periodically reads this history and dumps it somewhere, usercan have continuous history of wait events.
• Waits profile. It's implemented as in-memory hash table where samplesare accumulated per each wait event and can be divided by process, wait event,query and other dimensions. 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 wait events over time.

In combination withpg_stat_statements this extension can also provideper query statistics.

pg_wait_sampling launches special background worker for gathering thestatistics above.

pg_wait_sampling is implemented as an extension and not available in defaultPostgreSQL installation. It is available fromgithubunder the same license asPostgreSQLand supports PostgreSQL 13+.

Pre-builtpg_wait_sampling packages are provided in official PostgreSQLrepository:https://download.postgresql.org/pub/repos/

pg_wait_sampling is PostgreSQL extension which requires PostgreSQL 13 orhigher. Before build and install you should ensure following:

• PostgreSQL version is 13 or higher.
• You have development package of PostgreSQL installed or you have builtPostgreSQL from source.
• Your PATH variable is configured so thatpg_config command available, orPG_CONFIG variable is set.

Typical installation procedure may look like this:

$ git clone https://github.com/postgrespro/pg_wait_sampling.git$ cd pg_wait_sampling$ make USE_PGXS=1$ sudo make USE_PGXS=1 install

Then addshared_preload_libraries = pg_wait_sampling topostgresql.conf andrestart the server.

To test your installation:

$ make USE_PGXS=1 installcheck

To create the extension in the target database:

CREATE EXTENSION pg_wait_sampling;

Compilation on Windows is not supported, since the extension uses symbols from PostgreSQLthat are not exported.

pg_wait_sampling interacts with user by set of views and functions.

pg_wait_sampling_current view – information about current wait events forall processes including background workers.

pg_wait_sampling_get_current(pid int4) returns the same table for single givenprocess.

pg_wait_sampling_history view – history of wait events obtained by sampling intoin-memory ring buffer.

pg_wait_sampling_reset_history() function resets the history.

pg_wait_sampling_profile view – profile of wait events obtained by sampling intoin-memory hash table.

pg_wait_sampling_reset_profile() function resets the profile.

The work of wait event statistics collector worker is controlled by followingGUCs.

Ifpg_wait_sampling.profile_pid is set to false, sampling profile wouldn't becollected in per-process manner. In this case the value of pid could wouldbe always zero and corresponding row contain samples among all the processes.

Ifpg_wait_sampling.profile_queries is set tonone,queryid field inviews will be zero. If it is set totop, queryIds only of top level statementsare recorded. If it is set toall, queryIds of nested statements are recorded.

Ifpg_wait_sampling.sample_cpu is set to true then processes that are notwaiting on anything are also sampled. The wait event columns for such processeswill be NULL.

pg_wait_sampling.history_dimenstions andpg_wait_sampling.profile_dimensionsdetermine what additional columns will be sampled inhistory/profile_extendedviews. Possible values areall,pid,event,query_id,role_id,database_id,leader_pid,backend_type,backend_state,backend_start,client_addr,client_hostname,application_nameand any combination of column names.event turns on and off both event and event_type columns.all cannot be used together with any other values and must be used alone.

Values of these GUC variables can be changed only in config file or with ALTER SYSTEM.Then you need to reload server's configuration (such as withpg_reload_conf function)for changes to take effect.

SeePostgreSQL documentationfor list of possible wait events.

Please, notice, thatpg_wait_sampling is still under development and whileit's stable and tested, it may contain some bugs. Don't hesitate to raiseissues at github withyour bug reports.

If you're lacking of some functionality inpg_wait_sampling and feeling powerto implement it then you're welcome to make pull requests.

• Alexander Korotkova.korotkov@postgrespro.ru, Postgres Professional,Moscow, Russia
• Ildus Kurbangalievi.kurbangaliev@gmail.com, Postgres Professional,Moscow, Russia