Using EDB Wait States

Suggest edits

When EDB Wait States is registered as one of the shared preload libraries, it probes each of the running sessions at regular intervals. For every session, it collects information such as:

  • The database to which it's connected.
  • The logged-in user of the session.
  • The query running in that session.
  • The wait events on which it's waiting.

This information is saved in a set of files in a user-configurable path and directory folder given by the edb_wait_states.directory parameter to add to the postgresql.conf file. The path must be a full, absolute path, not a relative path.

Exploring data with the interface

Each of the functions in the EDB Wait States interface has common input and output parameters. Those parameters are:

Parameter(s)Input or outputDescription
start_ts and end_tsInputTogether these specify the time interval and the data to read. If you specify only start_ts, the data starting from start_ts is output. If you specify only end_ts, data up to end_ts is output. If you don't specify either, all the data is output.
query_idOutputIdentifies a normalized query. It's internal hash code computed from the query.
session_idOutputIdentifies a session.
ref_start_ts and ref_end_tsOutputThe timestamps of a file containing a particular data point. A data point might be a wait event sample record, a query record, or a session record.
wait_timeOutputThe amount of time in seconds spent waiting for some wait events.
cpu_timeOutputThe amount of time in seconds spent working on the CPU. For this given duration, the query wasn't waiting on any wait event.
db_timeOutputThe sum of the wait_time and the cpu_time. The db_time, wait_time, and the cpu_time don't provide an exact time. They provide an approximate time computed based on number of occurrences and the sampling interval.

The following examples use a scenario where three queries are executed simultaneously on four different sessions connected to different databases using different users. Those three queries are:

SELECT schemaname FROM pg_tables, pg_sleep(15) WHERE schemaname <>
'pg_catalog'; /* ran on 2 sessions */
SELECT tablename FROM pg_tables, pg_sleep(10) WHERE schemaname <>
'pg_catalog';
SELECT tablename, schemaname FROM pg_tables, pg_sleep(10) WHERE schemaname
<> 'pg_catalog';

edb_wait_states_data

Use this function to read the data collected by the BGW:

edb_wait_states_data(
  IN start_ts timestamptz default '-infinity'::timestamptz,
  IN end_ts timestamptz default 'infinity'::timestamptz,
  OUT session_id int4,
  OUT <dbname> text,
  OUT <username> text,
  OUT <query> text,
  OUT <query_start_time> timestamptz,
  OUT <sample_time> timestamptz,
  OUT <wait_event_type> text,
  OUT <wait_event> text
)

You can use this function to find out the following:

  • The queries running in the given duration (defined by start_ts and end_ts) in all the sessions, and the wait events, if any, they were waiting on. For example:

    SELECT query, session_id, wait_event_type, wait_event
  FROM edb_wait_states_data(start_ts, end_ts);

  • The progress of a session within a given duration, that is, the queries run in a session (session_id = 100000) and the wait events the queries waited on. For example:

    SELECT query, wait_event_type, wait_event
  FROM edb_wait_states_data(start_ts, end_ts)
  WHERE session_id = 100000;

  • The duration for which the samples are available. For example:

    SELECT min(sample_time), max(sample_time)
  FROM edb_wait_states_data();

Parameters

In addition to the common parameters described previously, each row of the output gives the following:

dbname — The session's database.

username — The session's logged-in user.

query — The query running in the session.

query_start_time — The time when the query started.

sample_time — The time when wait event data was collected.

wait_event_type — The type of wait event the session (backend) is waiting on.

wait_event — The wait event the session (backend) is waiting on.

Example

This example shows sample output from the edb_wait_states_data() function:

edb=# SELECT * FROM edb_wait_states_data();
Output
-[ RECORD 1 ]----+-------------------------------------------------------------------------
session_id       | 4398
dbname           | edb
username         | enterprisedb
query            | SELECT schemaname FROM pg_tables, pg_sleep($1) WHERE schemaname <> $2
query_start_time | 17-AUG-18 11:56:05.271962 -04:00
sample_time      | 17-AUG-18 11:56:19.700236 -04:00
wait_event_type  | Timeout
wait_event       | PgSleep
-[ RECORD 2 ]----+-------------------------------------------------------------------------
session_id       | 4398
dbname           | edb
username         | enterprisedb
query            | SELECT schemaname FROM pg_tables, pg_sleep($1) WHERE schemaname <> $2
query_start_time | 17-AUG-18 11:56:05.271962 -04:00
sample_time      | 17-AUG-18 11:56:18.699938 -04:00
wait_event_type  | Timeout
wait_event       | PgSleep
-[ RECORD 3 ]----+-------------------------------------------------------------------------
session_id       | 4398
dbname           | edb
username         | enterprisedb
query            | SELECT schemaname FROM pg_tables, pg_sleep($1) WHERE schemaname <> $2
query_start_time | 17-AUG-18 11:56:05.271962 -04:00
sample_time      | 17-AUG-18 11:56:17.700253 -04:00
wait_event_type  | Timeout
wait_event       | PgSleep
                    .
                    .
                    .

edb_wait_states_queries

This function gives information about the queries sampled by the BGW. For example:

edb_wait_states_queries(
  IN start_ts timestamptz default '-infinity'::timestamptz,
  IN end_ts timestamptz default 'infinity'::timestamptz,
  OUT query_id int8,
  OUT <query> text,
  OUT ref_start_ts timestamptz
  OUT ref_end_ts timestamptz
)

A new queries file is created periodically. Multiple query files can be generated corresponding to specific intervals.

This function returns all the queries in query files that overlap with the given time interval. A query gives all the queries in query files that contained queries sampled between start_ts and end_ts:

SELECT query FROM edb_wait_states_queries(start_ts, end_ts);

In other words, the function can output queries that didn't run in the given interval. To do that, use edb_wait_states_data().

Parameters

In addition to the common parameters described previously, each row of the output gives the following:

query — Normalized query text.

Example

This example shows sample output from the edb_wait_states_queries() function:

edb=# SELECT * FROM edb_wait_states_queries();
Output
-[ RECORD 1 ]+-----------------------------------------------------------------------------
query_id     | 4292540138852956818
query        | SELECT schemaname FROM pg_tables, pg_sleep($1) WHERE schemaname <> $2
ref_start_ts | 17-AUG-18 11:52:38.698793 -04:00
ref_end_ts   | 18-AUG-18 11:52:38.698793 -04:00
-[ RECORD 2 ]+-----------------------------------------------------------------------------
query_id     | 3754591102365859187
query        | SELECT tablename FROM pg_tables, pg_sleep($1) WHERE schemaname <> $2
ref_start_ts | 17-AUG-18 11:52:38.698793 -04:00
ref_end_ts   | 18-AUG-18 11:52:38.698793 -04:00
-[ RECORD 3 ]+-----------------------------------------------------------------------------
query_id     | 349089096300352897
query        | SELECT tablename, schemaname FROM pg_tables, pg_sleep($1) WHERE schemaname <> $2
ref_start_ts | 17-AUG-18 11:52:38.698793 -04:00
ref_end_ts   | 18-AUG-18 11:52:38.698793 -04:00

edb_wait_states_sessions

This function gives information about the sessions sampled by the BGW:

edb_wait_states_sessions(
  IN start_ts timestamptz default '-infinity'::timestamptz,
  IN end_ts timestamptz default 'infinity'::timestamptz,
  OUT session_id int4,
  OUT <dbname> text,
  OUT <username> text,
  OUT ref_start_ts timestamptz,
  OUT ref_end_ts timestamptz,
  OUT application_name text,
  OUT client_hostname text,
  OUT session_start_ts timestamptz
)

You can use this function to identify the databases that were connected and the users that started those sessions. For example:

SELECT dbname, username, session_id
  FROM edb_wait_states_sessions();

Similar to edb_wait_states_queries(), this function outputs all the sessions logged in session files that contain sessions sampled in the given interval. It doesn't necessarily output only the sessions sampled in the given interval. To identify that, use edb_wait_states_data().

Parameters

In addition to the common parameters described previously, each row of the output gives the following:

dbname — The database to which the session is connected.

username — Login user of the session.

application_name — Name of the application connection to the session.

client_hostname — Host name of the client machine.

session_start_ts — Actual timestamp when the session started.

Example

This example shows sample output from the edb_wait_states_sessions() function:

edb=# SELECT * FROM edb_wait_states_sessions();
Output
-[ RECORD 1 ]---+---------------------------------
session_id       | 184365
dbname           | postgres
username         | dilip
ref_start_ts     | 01-FEB-24 15:59:56.283204 +05:30
ref_end_ts       | 02-FEB-24 15:59:56.283204 +05:30
application_name | pgbench
client_hostname  |
session_start_ts | 01-FEB-24 16:15:13.267287 +05:30
-[ RECORD 2 ]---+---------------------------------
session_id       | 184344
dbname           | postgres
username         | dilip
ref_start_ts     | 01-FEB-24 15:59:56.283204 +05:30
ref_end_ts       | 02-FEB-24 15:59:56.283204 +05:30
application_name | pgbench
client_hostname  |
session_start_ts | 01-FEB-24 16:15:59.284207 +05:30

edb_wait_states_samples

This function gives information about wait events sampled by the BGW:

edb_wait_states_samples(
  IN start_ts timestamptz default '-infinity'::timestamptz,
  IN end_ts timestamptz default 'infinity'::timestamptz,
  OUT query_id int8,
  OUT session_id int4,
  OUT <query_start_time> timestamptz,
  OUT <sample_time> timestamptz,
  OUT <wait_event_type> text,
  OUT <wait_event> text,
  OUT sampling_interval int4
)

Usually, you don't need to call this function directly.

Parameters

In addition to the common parameters described previously, each row of the output gives the following:

query_start_time — The time when the query started in this session.

sample_time — The time when wait event data was collected.

wait_event_type — The type of wait event on which the session is waiting.

wait_event — The wait event on which the session (backend) is waiting.

sampling_interval — The time interval at which the sample is taken.

Example

This example shows sample output from the edb_wait_states_samples() function:

edb=# SELECT * FROM edb_wait_states_samples();
Output
-[ RECORD 1 ]-----+---------------------------------
query_id          | -5489517304104177538
session_id        | 183864
query_start_time  | 01-FEB-24 16:04:40.292778 +05:30
sample_time       | 01-FEB-24 16:04:41.284071 +05:30
wait_event_type   | Timeout
wait_event        | PgSleep
sampling_interval | 1
-[ RECORD 2 ]-----+---------------------------------
query_id          | -5489517304104177538
session_id        | 183864
query_start_time  | 01-FEB-24 16:04:40.292778 +05:30
sample_time       | 01-FEB-24 16:04:42.284278 +05:30
wait_event_type   | Timeout
wait_event        | PgSleep
sampling_interval | 1
-[ RECORD 3 ]-----+---------------------------------
query_id          | -5489517304104177538
session_id        | 183864
query_start_time  | 01-FEB-24 16:04:40.292778 +05:30
sample_time       | 01-FEB-24 16:04:43.283385 +05:30
wait_event_type   | Timeout
wait_event        | PgSleep
sampling_interval | 1                    .
                    .
                    .

edb_wait_states_purge

The function deletes all the sampled data files (queries, sessions, and wait-event samples) that were created after start_ts and aged (rotated) before end_ts:

edb_wait_states_purge(
  IN start_ts timestamptz default '-infinity'::timestamptz,
  IN end_ts timestamptz default 'infinity'::timestamptz
)

Usually you don't need to run this function. The backend usually purges those according to the retention age. However, if that doesn't happen for some reason, you can use this function.

To find out how long the samples were retained, use edb_wait_states_data().

Example

This code shows the $PGDATA/edb_wait_states directory before running edb_wait_states_purge():

[root@localhost data]# pwd
/var/lib/edb/as14/data
[root@localhost data]# ls -l edb_wait_states
total 12
-rw------- 1 enterprisedb ...  253 Aug 17 11:56 edb_ws_queries_587836358698793_587922758698793
-rw------- 1 enterprisedb ... 1600 Aug 17 11:56 edb_ws_samples_587836358698793_587839958698793
-rw------- 1 enterprisedb ...   94 Aug 17 11:56 edb_ws_sessions_587836358698793_587922758698793

This code shows the $PGDATA/edb_wait_states directory after running edb_wait_states_purge():

edb=# SELECT * FROM edb_wait_states_purge();
Output
 edb_wait_states_purge
-----------------------

(1 row)
[root@localhost data]# pwd
/var/lib/edb/as14/data
[root@localhost data]# ls -l edb_wait_states
total 0

Deactivating

To disable EDB Wait States from collecting more data, enter: 

ALTER SYSTEM SET edb_wait_states.enable_collection TO OFF;
SELECT pg_reload_conf();

To reenable EDB Wait States, set edb_wait_states.enable_collection to ON.

You can check whether the EDB Wait States is collecting data using a SHOW command. In the following example, data collection is enabled in EDB Wait States:

edb=#SHOW edb_wait_states.enable_collection;
Output
 edb_wait_states.enable_collection 
-----------------------------------
 on
(1 row)

edb_wait_states_system_info

The function outputs the hostname, CPU information, and memory information for the server machine.

edb_wait_states_system_info(
  OUT host_name text,
  OUT cpu_info text,
  OUT mem_info text
)

Parameters

Each row of the output gives the following information:

host_name — The hostname of the server machine.

cpu_info — CPU information about the server machine.

mem_info — Memory information about the server machine.

Example

This example shows sample output from the edb_wait_states_system_info() function:

edb=# select * from edb_wait_states_system_info();
-[ RECORD 1 ]-------------------------------------------------------------------
host_name | dilip_kumar                                                                                                                                                                                            
          | 
cpu_info  | processor       : 0                                                                                                                                                                                    
          | BogoMIPS        : 48.00                                                                                                                                                                               
          | Features        : fp asimd evtstrm aes pmull sha1 sha2 crc32 atomics 
          | CPU implementer : 0x61                                                                                                                                                                                 
          | CPU architecture: 8                                                                                                                                                                                    
          | CPU variant     : 0x0                                                                                                                                                                                  
          | CPU part        : 0x000                                                                                                                                                                                
          | CPU revision    : 0                                                                                                                                                                                    
          |                                                                                                                                                                                                       
          | processor       : 1                                                                                                                                                                                  
          | BogoMIPS        : 48.00                                                                                                                                                                                
          | Features        : fp asimd evtstrm aes pmull sha1 sha2 crc32 atomics
          | CPU implementer : 0x61                                                                                                                                                                                 
          | CPU architecture: 8                                                                                                                                                                                    
          | CPU variant     : 0x0                                                                                                                                                                                  
          | CPU part        : 0x000                                                                                                                                                                                
          | CPU revision    : 0                                                                                                                                                                         
            ...... 
          | 
mem_info  | MemTotal:        7786664 kB                                                                                                                                                                            
          | MemFree:          422544 kB                                                                                                                                                                            
          | MemAvailable:    2044836 kB                                                                                                                                                                            
          | Buffers:             256 kB                                                                                                                                                                            
          | Cached:          1884380 kB                                                                                                                                                                            
          | SwapCached:       146316 kB                                                                                                                                                                            
          | Active:          3180572 kB                                                                                                                                                                            
          | Inactive:        2843108 kB                                                                                                                                                                            
          | Active(anon):    2448468 kB                                                                                                                                                                            
          | Inactive(anon):  1960812 kB                                                                                                                                                                            
          | Active(file):     732104 kB
          .......

edb_wait_states_wait_events

The function outputs aggregated wait event information.

edb_wait_states_wait_events(
  IN start_ts timestamptz default '-infinity'::timestamptz,
  IN end_ts timestamptz default 'infinity'::timestamptz,
  OUT waitevent TEXT,
  OUT wait_event_type text,
  OUT waittime int8,
  OUT pct_dbtime numeric
)

Parameters

In addition to the common parameters described previously, each row of the output gives the following information:

waitevent — The name of the wait event.

wait_event_type — The type of wait event.

waittime — The approximate wait time of this wait event (in seconds) based on the number of samples and the sampling interval from edb_wait_states_samples.

pct_dbtime — The percentage of database time spent on this wait event.

Example

This example shows sample output from the edb_wait_states_wait_events() function:

edb=# select * from edb_wait_states_wait_events();
-[ RECORD 1 ]---+------------------------
waitevent       | 
wait_event_type | 
waittime        | 124
pct_dbtime      | 36.5781710914454277
-[ RECORD 2 ]---+------------------------
waitevent       | DataFileRead
wait_event_type | IO
waittime        | 92
pct_dbtime      | 27.1386430678466077
-[ RECORD 3 ]---+------------------------
waitevent       | WALWrite
wait_event_type | IO
waittime        | 3
pct_dbtime      | 0.88495575221238938053
-[ RECORD 4 ]---+------------------------
waitevent       | BufFileWrite
wait_event_type | IO
waittime        | 3
pct_dbtime      | 0.88495575221238938053
                .
                .
                .

edb_wait_states_sql_statements

The function outputs database time, CPU time, and wait information for each SQL statement.

edb_wait_states_sql_statements(
  IN start_ts timestamptz default '-infinity'::timestamptz,
  IN end_ts timestamptz default 'infinity'::timestamptz,
  OUT query_id int8,
  OUT dbtime numeric,
  OUT waittime numeric,
  OUT cputime numeric,
  OUT top_waitevent text,
  OUT query text
)

Parameters

In addition to the common parameters described previously, each row of the output gives the following information:

query_id — The query ID of the SQL statement.

dbtime — The total database time consumed by this statement. (Database time is the total time in seconds used to execute this statement, including CPU time as well as wait time).

waittime — The approximate wait time spent by this query.

cputime — The approximate CPU time spent by this query.

top_waitevent — The wait event on which this statement spent maximum time.

query — The actual text of the query.

Example

This example shows sample output from the edb_wait_states_sql_statements() function:

edb=# select * from edb_wait_states_sql_statements();
-[ RECORD 1 ]-+---------------------------------------------------
query_id      | -1697985474390439145
dbtime        | 188
waittime      | 188
cputime       | 0
top_waitevent | DataFileRead
query         | vacuum analyze pgbench_accounts
-[ RECORD 2 ]-+---------------------------------------------------
query_id      | 2577670717561330585
dbtime        | 143
waittime      | 52
cputime       | 91
top_waitevent | WALSync
query         | copy pgbench_accounts from stdin with (freeze on)
-[ RECORD 3 ]-+---------------------------------------------------
query_id      | -7684589253409855891
dbtime        | 250
waittime      | 204
cputime       | 46
top_waitevent | WALWrite
query         | alter table pgbench_accounts add primary key (aid)

edb_wait_states_cluster_stats

The function outputs database, WAL, and session count information for a given time period. Each row shows the consolidated information for that time period.

edb_wait_states_cluster_stats(
  IN start_ts timestamptz default '-infinity'::timestamptz,
  IN end_ts timestamptz default 'infinity'::timestamptz,
  OUT num_sessions int4,
  OUT num_databases int4,
  OUT wal_records int8,
  OUT wal_fpi int8,
  OUT wal_bytes int8,
  OUT xact_commit int8,
  OUT xact_rollback int8,
  OUT blocks_fetched int8,
  OUT blocks_hit int8,
  OUT tuples_returned int8,
  OUT tuples_fetched int8,
  OUT tuples_inserted int8,
  OUT tuples_updated int8,
  OUT tuples_deleted int8,
  OUT temp_files int8,
  OUT temp_bytes int8,
  OUT sample_ts timestamptz,
  OUT last_wal_stats_reset timestamptz,
  OUT last_db_stats_reset timestamptz
)

Parameters

In addition to the common parameters described previously, each row of the output gives the following information:

num_sessions — Number of active sessions.

num_databases — Total number of databases.

wal_records — Total number of WAL records generated.

wal_fpi — Total number of WAL full page images generated.

wal_bytes — Total amount of WAL generated in bytes.

xact_commit — Number of transactions that have been committed.

xact_rollback — Number of transactions that have been rolled back.

blocks_fetched — Number of disk blocks accessed.

blocks_hit — Number of times disk blocks were found already in the buffer cache.

tuples_returned — Number of live rows fetched by sequential scans and index entries returned by index scans.

tuples_fetched — Number of live rows fetched by index scans.

tuples_inserted — Number of rows inserted by queries.

tuples_updated — Number of rows updated by queries.

tuples_deleted — Number of rows deleted by queries.

temp_files — Number of temporary files created by queries.

temp_bytes — Total amount of data written to temporary files by queries.

sample_ts — Timestamp when this sample was taken.

last_wal_stats_reset — Time when WAL statistics were last reset.

last_db_stats_reset — Time when database statistics were last reset.

Example

This example shows sample output from the edb_wait_states_cluster_stats() function:

edb=# select * from edb_wait_states_cluster_stats();
-[ RECORD 1 ]--------+---------------------------------
num_sessions         | 0
num_databases        | 4
wal_records          | 4358683
wal_fpi              | 8593
wal_bytes            | 419918702
xact_commit          | 557135
xact_rollback        | 143
blocks_fetched       | 30959799
blocks_hit           | 30955075
tuples_returned      | 3968546
tuples_fetched       | 2179331
tuples_inserted      | 793226
tuples_updated       | 1667862
tuples_deleted       | 2235
temp_files           | 8
temp_bytes           | 4063232
sample_ts            | 01-FEB-24 15:59:57.283591 +05:30
last_wal_stats_reset | 01-FEB-24 15:49:12.976401 +05:30
last_db_stats_reset  | 01-JAN-00 05:30:00 +05:30
-[ RECORD 2 ]--------+---------------------------------
num_sessions         | 0
num_databases        | 4
wal_records          | 4358683
wal_fpi              | 8593
wal_bytes            | 419918702
xact_commit          | 557135
xact_rollback        | 143
blocks_fetched       | 30959799
blocks_hit           | 30955075
tuples_returned      | 3968546
tuples_fetched       | 2179331
tuples_inserted      | 793226
tuples_updated       | 1667862
tuples_deleted       | 2235
temp_files           | 8
temp_bytes           | 4063232
sample_ts            | 01-FEB-24 15:59:58.285452 +05:30
last_wal_stats_reset | 01-FEB-24 15:49:12.976401 +05:30
last_db_stats_reset  | 01-JAN-00 05:30:00 +05:30

edb_wait_states_cluster

The function outputs differential information for edb_wait_states_cluster_stats derived from two snapshots.

edb_wait_states_cluster(
  IN start_ts timestamptz default '-infinity'::timestamptz,
  IN end_ts timestamptz default 'infinity'::timestamptz,
  OUT start_session int4,
  OUT end_session int4,
  OUT wal_records int8,
  OUT wal_fpi int8,
  OUT wal_bytes int8,
  OUT xact_commit int8,
  OUT xact_rollback int8,
  OUT blocks_fetched int8,
  OUT blocks_hit int8,
  OUT tuples_returned int8,
  OUT tuples_fetched int8,
  OUT tuples_inserted int8,
  OUT tuples_updated int8,
  OUT tuples_deleted int8,
  OUT temp_files int8,
  OUT temp_bytes int8
)

Parameters

The output for this function is the same as edb_wait_states_cluster_stats(). But instead of producing one record for each sample the function produces a single record with differential values between start_ts and end_ts.

Note

If the pg_stat_wal or pg_stat_database is reset or any database is dropped between the start_ts and end_ts, the output for the respective fields is -1. If the database is dropped and re-created, it might not be reliably detected and differential values might not be accurate.

Example

This example shows sample output from the edb_wait_states_cluster() function:

edb# select * from edb_wait_states_cluster();
-[ RECORD 1 ]---+------------
start_session   | 0
end_session     | 1
wal_records     | 7723654
wal_fpi         | 481549
wal_bytes       | 14740067381
xact_commit     | 550880
xact_rollback   | 75
blocks_fetched  | 38997085
blocks_hit      | 37112621
tuples_returned | 116057913
tuples_fetched  | 2203619
tuples_inserted | 100546427
tuples_updated  | 1606127
tuples_deleted  | 125
temp_files      | 4
temp_bytes      | 2004926464

Could this page be better? Report a problem or suggest an addition!