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.
  --create    Create the external table and views.
  --drop      Drop the external table and views.
  -h, --help  Display this message.
  -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

Currently, only gp_terminate_backend and gp_cancel_backend events are logged and accessible in the gp_wlm_events view.

The following table describes the contents of the gp_wlm_events view.

Column name Type Description
type text The type of the event: RULE_ACTION, USER_ACTION, ALERT, or INVALID. The pg_terminate_backend and host:pg_cancel_backend actions are RULE_ACTION.
level text The logging level of the message: err, warn, info, debug, or trace. A pg_terminate_backend or host:pg_cancel_backend event is logged at the info level.
time timestamp The time the event record was created.
host text The host on which the event occurred.
source text The component that triggered the event.
message text A message identifying the event. For pg_terminate_backend the message is “pg_terminate_backend”.
context text A comma-delimited list of context arguments.
data text This is the rule expression.

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 a pg_terminate_backend row in the gp_wlm_events view:

 type        | level |        time         | host      |    source     |       message        |           context          |                           data
RULE_ACTION  | info  | 2016-06-20 20:40:14 | sdw1      | gpdb_throttle | pg_terminate_backend | runtime=8,session_id=44384 | pg_terminate_backend() when session_id:host:pid:runtime > 0