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

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.

The manage-event-tables.sh utility script creates external tables to access the log files and a view to consolidate the external tables so that you can query these event records from within a database. The external tables and view must first be created using the manage-event-tables.sh script. The external tables are created in the postgres database by default, but you can specify a different database when you create the tables.

Setting Up the gp_wlm_events View

The manage-event-tables.sh script creates, recreates, or drops the external tables and gp_wlm_events view.

To see the syntax, log in to the Greenplum master host as the gpadmin user and run the script with the -h (--help) option:

$ <INSTALL_DIR>/bin/manage-event-tables.sh --help
Manage the gp-wlm external event tables.
Commands:
  --create    Create the external table and views.
  --drop      Drop the external table and views.
  -h, --help  Display this message.
Options:
  -d, --dbname=NAME    Use database NAME. Default is postgres.
  -q, --quiet          Silence stdout.

To create (or recreate) the external tables and the gp_wlm_events view, run the script with the --create flag. If you want to create the tables and view in a database other than postgres, include the --dbname option.

<INSTALL_DIR>/bin/manage-event-tables.sh --create --dbname=<database-name>

To delete the tables and views from a database other than postgres, you must include the --dbname option with the --drop option.

Using the gp_wlm_events View

The gp_terminate_backend and pg_cancel_backend events are logged and accessible in the pg_wlm_events view.

The following table describes the contents of the gp_wlm_events view.

Column name Type Description
ident 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.
sess_id integer The ID of the session that matched this rule trigger.
hostname text The host on which the event occurred.
usename text The role name from the session that matched this rule trigger.
current_query text The text of the current query in the session.
datname 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.

The sess_id, usename, current_query, datname, and application_name columns match columns with the same 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 ]----+--------------------------------------------------------------------------------
ident            | e7054d71-293b-4bce-a3bb-caafbcbc6758
time             | 2017-01-31 19:31:02-08
rulename         | test
action           | pg_cancel_backend
sess_id          | 4200
hostname         | localhost.localdomain
usename          | pivotal
current_query    | select pg_sleep(10);
datname          | 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
-[ RECORD 2 ]----+--------------------------------------------------------------------------------
ident            | 0c1f50dd-e1fc-4dd8-9829-7e450f74fde8
time             | 2017-01-31 19:37:30-08
rulename         | test2
action           | pg_terminate_backend
sess_id          | 4226
hostname         | localhost.localdomain
usename          | pivotal
current_query    | <IDLE>
datname          | postgres
application_name | psql
context          | runtime=8,session_id=4226
rule             | pg_terminate_backend() when session_id:host:pid:runtime > 5