Table of Contents Previous Next


8 Performance Analysis and Tuning : 8.2 EDB Wait States

The EDB wait states contribution module contains two main components.
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 be added to the postgresql.conf file. The specified path must be a full, absolute path and not a relative path.
Step 1: EDB wait states is installed with the edb-asxx-server-edb-modules RPM package where xx is the Advanced Server version number.
Step 2: To launch the worker, it must be registered in the postgresql.conf file using the shared_preload_libraries parameter, for example:
Step 3: Restart the database server. After a successful restart, the background worker begins collecting data.
Step 4: To review the data, create the following extension:
Step 5: To terminate the EDB wait states worker, remove $libdir/edb_wait_states from the shared_preload_libraries parameter and restart the database server.
Step 1: EDB wait states module is installed with the EDB Modules installer by invoking StackBuilder Plus utility. Follow the onscreen instructions to complete the installation of the EDB Modules.
Step 2: To register the worker, modify the postgresql.conf file to include the wait states library in the shared_preload_libraries configuration parameter. The parameter value must include:
The EDB wait states installation places the edb_wait_states.dll library file in the following path:
Step 3: Restart the database server for the changes to take effect. After a successful restart, the background worker gets started and starts collecting the data.
Step 4: To view the data, create the following extension:
The installer places the edb_wait_states.control file in the following path:
To terminate the EDB wait states worker, use the DROP EXTENSION command to drop the edb_wait_states extension; then modify the postgresql.conf file, removing $libdir/edb_wait_states.dll from the shared_preload_libraries parameter. Restart the database server after modifying the postgresql.conf file to apply your changes.
start_ts and end_ts (IN). Together these specify the time interval and the data within which is to be read. If only start_ts is specified, the data starting from start_ts is output. If only end_ts is provided, data up to end_ts is output. If none of those are provided, all the data is output. Every function outputs different data. The output of each function will be explained below.
query_id (OUT). Identifies a normalized query. It is internal hash code computed from the query.
session_id (OUT). Identifies a session.
ref_start_ts and ref_end_ts (OUT). Provide the timestamps of a file containing a particular data point. A data point may be a wait event sample record or a query record or a session record.
Note: The examples shown in the following sections are based on the following three queries executed on four different sessions connected to different databases using different users, simultaneously:
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
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:
The following is a sample output from the edb_wait_states_data() function.
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
The following is a sample output from the edb_wait_states_queries() function.
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
Similar to edb_wait_states_queries(), this function outputs all the sessions logged in session files that contain sessions sampled within the given interval and not necessarily only the sessions sampled within the given interval. To identify that one should use edb_wait_states_data().
The following is a sample output from the edb_wait_states_sessions() function.
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
The following is a sample output from the edb_wait_states_samples() function.
IN start_ts timestamptz default '-infinity'::timestamptz,
IN end_ts timestamptz default 'infinity'::timestamptz
In order to know the duration for which the samples have been retained, use edb_wait_states_data() as explained in the previous examples of that function.
The $PGDATA/edb_wait_states directory before running edb_wait_states_purge():
The $PGDATA/edb_wait_states directory after running edb_wait_states_purge():

8 Performance Analysis and Tuning : 8.2 EDB Wait States

Table of Contents Previous Next