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
id 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.
rule_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.
time timestamptz The time (with time zone) the record was created.
query_start timestamptz The time the query started executing.
rulename text The name of the Workload Mangager rule that was matched.
session_id integer ID of the session that was running the matched query.
pid integer ID of the process that was running the matched query.
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 name of the database.
application_name text The name of the client application that executed the query, for example psql.
context text A comma-delimited list of rule-specific contextual metrics.
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.
message text The message that was passed as a parameter to the gpdb_record action.
rule text The rule expression.
current_query text The text of the current query in the session.

The primary identifier of each entry in the table is the id 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 ]-----+-----------------------------------------------------------------
id                | 7a3e65b6-cd89-40ba-83ae-a502b7c480cf
rule_state        | BEGIN
time              | 2017-06-23 17:38:38-05
query_start       | 2017-06-23 17:38:29.502544-05
rulename          | over120
session_id        | 27194
pid               | 118516
hostname          | mdw
username          | gpadmin
db_name           | postgres
application_name  | psql
context           | runtime=121
gpdb_segment_role | GPDB_MASTER
message           | Query exceeds 120 seconds.
rule              | gpdb_record(message="Query exceeds 120 seconds.") when session_id:host:pid:runtime > 120
current_query     | delete from test where f1()
-[ RECORD 2 ]-----+-----------------------------------------------------------------
id                | 7a3e65b6-cd89-40ba-83ae-a502b7c480cf
rule_state        | END
time              | 2017-06-23 17:39:20-05
query_start       | 
rulename          | 
session_id        | 
pid               | 
hostname          | 
username          | 
db_name           | 
application_name  | 
context           | 
gpdb_segment_role | 
message           | 
rule              | 
current_query     | 

In the above example, the rule_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.