Querying Workload Manager Record Data

The gp_wlm_records table contains a record of events describing where, why, and how the gpdb_record action was triggered by a rule on the Greenplum cluster.

The gp_wlm_records table is created in the postgres database by default. A different database can be specified at installation time with the --dbname-records installation option.

The table has the following structure:

Column Type Description
time timestamptz The time (with time zone) the record was created.
state text The state of the rule. Possible values are BEGIN and END. WLM creates a record with a state of BEGIN when a query begins to match a rule, and a second record with a state of END when the query no longer matches.
ident text A unique identifier for each time a rule matches a query. Each unique value exists in exactly two rows: one in which the value for the state column is BEGIN and the other in which the value for the state column is END. The BEGIN row indicates when a rule condition begins to match a query and the END row when the condition ends matching.
hostname text The host on which the event occurred.
query_start text The time the query started executing.
message text The message that was passed as a parameter to the gpdb_record action.
pid integer ID of the process that was running the matched query.
session_id integer ID of the session that was running the matched query.
gpdb_segment_role text The role of the segment that matched the condition. It can be one of three possible values: GPDB_MASTER, GPDB_SEGMENT, or GPDB_MIRROR.
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.
rule text The rule expression.
context_args text A comma-delimited list of rule-specific contextual metrics.

The primary identifier of each entry in the table is the ident column. This column stores a unique identifier that represents a specific rule that triggered on a specific node in the cluster. If a rule triggers on more than one node in the cluster at the same time, each node is treated as a separate event and receives a unique identifier.

Following are two sample entries from the gp_wlm_records table. In this example, a rule was created to track when a query runs for more than 120 seconds:

=# \x on
Expanded display is on.
=# select * from  gp_wlm_records;
-[ RECORD 1 ]-----+-----------------------------------------------------------------------------------------
time              | Fri Jun 17 14:30:27 2016
state             | BEGIN
ident             | 36b3369d-0be8-4d98-b116-6d55f1caf122
hostname          | sdw2
query_start       | 2016-06-17 14:28:24.162044-07
message           | Query exceeds 120 seconds.
pid               | 98885
session_id        | 1112
gpdb_segment_role | GPDB_SEGMENT
usename           | gpadmin
current_query     | delete from test where f1();
rule              | gpdb_record(message="Query exceeds 120 seconds.") when session_id:host:pid:runtime > 120
context_args      | runtime=121
-[ RECORD 2 ]-----+-----------------------------------------------------------------------------------------
time              | Fri Jun 17 14:31:07 2016
state             | END
ident             | 36b3369d-0be8-4d98-b116-6d55f1caf122
hostname          | 
query_start       | 
message           | 
pid               | 
session_id        | 
gpdb_segment_role | 
usename           | 
current_query     | 
rule              | 
context_args      | 

In the above example, the state column represents when a query began triggering a rule on a given node and when it stopped. The hostname column stores the host on which the rule triggered.