Movatterモバイル変換

1. Addpg_wait_sampling to theshared_preload_libraries variable in thepostgresql.conf file:

   shared_preload_libraries = 'pg_wait_sampling'

   Important

   When using thepg_wait_sampling with thepg_stat_statements extension, placepg_stat_statements beforepg_wait_sampling in theshared_preload_libraries variable. This ensures that thepg_stat_statements extension does not overwrite query IDs that are used bypg_wait_sampling.

2. Create thepg_wait_sampling extension using the following query:

   CREATE EXTENSION pg_wait_sampling;

3. Restart the server. Sincepg_wait_sampling requires additional shared memory and launches a background worker, you must restart the server after adding or removingpg_wait_sampling.

Whenpg_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 thepg_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, runpg_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 thepg_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 thepg_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 thepg_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 thepg_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.

If you are not interested in the distribution of wait events between processes, you can set thepg_wait_sampling.profile_pid variable tofalse. In this case, thepid value for each process is set to zero, and wait samples for all the processes are stored together.

Thepg_wait_sampling extension is configured with GUC variables. They are stored in the local memory and can be changed in thepostgresql.conf file or using theALTER SYSTEM command. For the changes to take effect, reload the server configuration. You can do it, for example, by calling thepg_reload_conf() function or executing thepg_ctlreload command.

• Alexander Korotkov

• Ildus Kurbangaliev<i.kurbangaliev@gmail.com>