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