CODEWITHSUNDEEP
postgresqlamazon-redshiftmetabase
Achyuth
Achyuth

Reputation: 33

Redshift: Detect and Cancel Long running queries Per User Connection

I am running Redshift queries with Metabase as the front end. I want to detect and cancel long running queries for a particular user. How do I automate this process with a script?

Upvotes: 3

Views: 14578

Answers (4)

Mahen Nakar
Mahen Nakar

Reputation: 394

In redshift under WML, edit workload queue and under it add custom rule define query execution type and action you want to execute.

Upvotes: 0

Yusuf Hassan
Yusuf Hassan

Reputation: 2013

Write a script and query the stv_recents table. It has pid and duration (in microseconds).

If a query crosses the threshold (in terms of duration), execute cancel pid.

You may put this script in a crontab and execute it at regular intervals.

Upvotes: 5

Joe Harris
Joe Harris

Reputation: 14035

There is no need to write this yourself!

You can use Redshift's built in Query Monitoring Rules ("QMR") to control queries according to a number of metrics such as return_row_count, query_execution_time, and query_blocks_read (among others).

Queries that exceed the limits defined in your rules can either log (no action), hop (move to a different queue), or abort (kill the query).

You can learn more in the Redshift Docs: "WLM Query Monitoring Rules"

Upvotes: 2

AlexYes
AlexYes

Reputation: 4208

This can be set as cluster configuration parameter on cluster or workload level. Workload is a separate query queue that is associated to specific users and defines the amount of cluster resources that is allowed to consume.

Read more about query timeouts here: statement_timeout and WLM Timeout

General info about workload management here: Configuring Workload Management

Upvotes: 0

Related Questions