LATEST VERSION: 3.3.1 - CHANGELOG
Pivotal Greenplum Command Center v3.3.1

Querying Workload Manager Event Data

When a Workload Manager rule successfully executes a pg_terminate_backend() or host:pg_cancel_backend() action to cancel a Greenplum Database query, the event is logged to a file on the host. You can use the gp_wlm_events view to query the logged events. The gp_wlm_events table is created during installation in the database specified with the Workload Manager installer --dbname command-line option. The default is the postgres database.

The following table describes the contents of the gp_wlm_events view.

Column name Type Description
id text A unique identifier for each row.
time timestamptz The time (with time zone) the event record was created.
rulename text The name of the triggered rule.
action text The component that triggered the event.
session_id integer The ID of the session that matched this rule trigger.
hostname text The host on which the event occurred.
username text The role name from the session that matched this rule trigger.
db_name text The database name of the session.
application_name text The name of the client application of the session that matched this rule trigger.
context text A comma-delimited list of rule-specific contextual metrics.
rule text The rule expression.
current_query text The text of the current query in the session.

The session_id, username, current_query, db_name, and application_name columns match columns with similar names in the pg_stat_activity system view row for the process that matched the rule trigger. See pg_stat_activity.

Since the view is based on external tables, each time you run a query, the view is refreshed from the event logs on the Greenplum hosts.

Following is an example of pg_cancel_backend and pg_terminate_backend rows in the gp_wlm_events view:

postgres=# select * from gp_wlm_events ;
-[ RECORD 1 ]----+--------------------------------------------------------------------------------
id               | e7054d71-293b-4bce-a3bb-caafbcbc6758
time             | 2017-07-31 19:31:02-08
rulename         | test
action           | pg_cancel_backend
session_id       | 4200
hostname         | localhost.localdomain
username         | pivotal
db_name          | postgres
application_name | psql
context          | runtime=6,host=localhost.localdomain,session_id=4200,host=localhost.localdomain
rule             | host:pg_cancel_backend() when session_id:host:pid:runtime > 5
current_query    | select pg_sleep(10);
-[ RECORD 2 ]----+--------------------------------------------------------------------------------
id               | 0c1f50dd-e1fc-4dd8-9829-7e450f74fde8
time             | 2017-07-31 19:37:30-08
rulename         | test2
action           | pg_terminate_backend
session_id       | 4226
hostname         | localhost.localdomain
username         | pivotal
db_name          | postgres
application_name | psql
context          | runtime=8,session_id=4226
rule             | pg_terminate_backend() when session_id:host:pid:runtime > 5
current_query    | <IDLE>

To see the old CSV event files, run this command, with a hostfile containing the names of all segment hosts. See gpssh in the Greenplum Database Utility Guide for instructions to create a host file.

$ gpssh -f <hostfile> -e "find <INSTALL_DIR>/gp-wlm/ -name 'events*.csv' -exec ls {} \;"

To delete the old CSV event files, run this command:

$ gpssh -f <hostfile> -e "find <INSTALL_DIR>/gp-wlm/ -name 'events*.csv' -exec rm {} \;"