Example Rules

This section provides examples of rules written for various purposes.

Note: Rules must be entered on a single line, but the rules shown in this section are wrapped for readability.

Record high cpu utilization queries

The following rule invokes the gpdb_record action when the gpadmin user runs a query and its total cpu utilization on a host exceeds 100%.

rule add simple gpdb_record(message="Too much cpu for gpadmin")
when session_id:host:total_cpu > 100
and session_id:host:pid:usename = 'gpadmin'

Throttle the cpu utilization of a query

This rule invokes the host:throttle_gpdb_query action when the cpu utilization of a process exceeds a threshold and the query has run for more than 20 seconds.

rule add throttle host:throttle_gpdb_query(max_cpu=30)
when host:pid:cpu_util > 20
and session_id:host:pid:usename = 'gpadmin'
and session_id:host:pid:runtime > 20

Cancel any query where the session has run longer than 120 seconds

This rule invokes the host:pg_cancel_backend action when a session_id:host:pid:runtime exceeds two minutes.

rule add kill_long host:pg_cancel_backend()
when session_id:host:pid:runtime > 120

Throttle and even out skew

This rule invokes host:throttle_gpdb_query when the total cpu usage of a query on a host exceeds 90% and the current query is a select on the skewtest table.

rule add skewrule host:throttle_gpdb_query(max_cpu=50)
when session_id:host:total_cpu > 100
and session_id:host:pid:current_query =~ /select.*skewtest/

You can observe the effects of this rule in the gptop GPDB Skew page.

Complex rule

This rule invokes gpdb_record for a query that meets the following criteria:

  • a query has total CPU usage greater than 90% on a host and has been running for more than 45 seconds, or
  • has cpu skew greater than 20%, and
  • is a select on a table that contains “test” in its name.
rule add comborule gpdb_record(message="My Message")
when ((session_id:host:total_cpu > 90 and session_id:host:pid:runtime > 45)
or session_id:cpu_skew > 20)
and session_id:host:pid:current_query =~ /select.*test/

The rule shows how you can group Boolean expressions with parentheses.

Record queries with high memory usage

This rule records a message when a query process exceeds 20% of the resident memory on a host.

rule add transient mem_high_segment_useage_20
gpdb_record(message=”MEM: high segment pctusage - 20%”) when
host:pid:resident_size_pct > 20
and session_id:host:pid:usename =~/.*/

Record queries with memory (rss) skew above 10%

This rule calls the gpdb_record action to log when memory skew exceeds 10% on a host.

rule add mem_skew_10 gpdb_record(message="MEM: query skew 10")
when session_id:resident_size_pct_skew > 10
and session_id:host:pid:usename =~/.*/