F.70. pg_wait_profile — sample-based profiling of database activity

F.70. pg_wait_profile — sample-based profiling of database activity
Prev	Up	Appendix F. Additional Supplied Modules and Extensions	Home	Next

---

F.70. pg_wait_profile — sample-based profiling of database activity #

F.70.1. Overview

F.70.2. Usage

Version: 1.0

F.70.1. Overview #

Tantor SE reports the current wait event for each backend. However, gathering descriptive statistical profile about server behavior requires repeated sampling of wait events. pg_wait_profile provides functions to sample database activity externally from client backends.

Caution

Extension just tracks backend processes having connection to any database. It ignores background workers performing some external to database tasks like log collector.

F.70.2. Usage #

F.70.2.1. Installation #

CREATE EXTENSION pg_wait_profile;

No need to setup extension to shared_preload_libraries

F.70.2.2. Functions #

wait_profile_session( pid int4, duration interval DEFAULT '10 seconds', sample_period interval DEFAULT '10 milliseconds', with_pids boolean DEFAULT false ) #

Samples processes that constitute a client session, including the main backend process and its related parallel workers, over the specified duration with the given sample_period.

wait_profile_procs( pids int4[], duration interval DEFAULT '10 seconds', sample_period interval DEFAULT '10 milliseconds', with_pids boolean DEFAULT false ) #

Samples all currently alive backend processes specified in the pids array over the specified duration with the given sample_period.

If pids is NULL, samples all backend processes in the current database.

wait_profile_all( duration interval DEFAULT '10 seconds', sample_period interval DEFAULT '10 milliseconds', with_pids boolean DEFAULT false ) #

Samples all backend processes in the current database over the specified duration with the given sample_period.

This function is equivalent to calling wait_profile_procs(NULL, ...).

All three functions output the following table structure:

Column	Type	Description	Details
pid	int4	Process ID	NULL when incoming options with_pids is false
backend_type	text	Type of backend	Possible values are identical to ones in the same column name in pg_stat_activity view
database_id	int4	Database ID
user_id	int4	User ID
state	text	Backend state	Possible values are identical to ones in the same column name in pg_stat_activity view
query_id	int8	Query identifier	NULL means the state out of query execution or fast path function calling or disabled query id computing on backend’s side
plan_id	int8	Plan identifier	Currently disabled and always NULL
event_type	text	Wait event type	CPU time means the "out of waiting" state, or an uncovered Tantor SE (unknown) wait.
event	text	Wait event name
count	int8	Number of samples

Note

To enable query_id computation there are two ways:

• set up explicitly compute_query_id to on (on specific backend or on a whole postgres cluster via ALTER SYSTEM SET)

• set up compute_query_id to auto (default value) or regress and extend shared_preload_libraries or session_preload_libraries to contain the pg_wait_profile extension

Caution

NULL value of query id has two meanings: out of query execution and disabled query id computing on backend’s side. As the second option might be turned off explicitly on session side, counting statistics for out of query execution will collapse with overall subsequent one after turning off of query id computing.

---

Prev	Up	Next
F.69. pg_visibility — visibility map information and utilities	Home	F.71. pg_wait_sampling — sampling based statistics of wait events