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 =~/.*/