3

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?

Achyuth
  • 33
  • 1
  • 1
  • 3
  • Did you end up using the WLM queue rules? I notice Metabase has a cancel button but doesn't seem to actually send a cancel to Redshift; queries continue to run. – Davos Apr 09 '19 at 11:31

4 Answers4

4

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.

Yusuf Hassan
  • 1,933
  • 1
  • 12
  • 21
  • Have you done that? Sounds a bit brutal, maybe restrict it to certain logins – Davos Apr 06 '19 at 10:49
  • 1
    @Davos Yes, had to implement this. Some business users simply don't understand the concept of partitions, indexing, cross joins etc. To keep resources consumed under check, the best way is to cancel such jobs [queries] which drain the resources, intimate them and if the trend continues, deactivate the account. – Yusuf Hassan Apr 09 '19 at 10:52
  • 1
    I'd say none understand :) Have you looked into the WLM queue rules? It could do this without needing a service to run a script. It has things like `nested_loop_join_row_count` to protect against cartesian products and other naive / greedy queries. I'm starting to agree with you that just killing rogue queries is the best kind of feedback to users who don't have visibility over the impact of their usage and the limited concurrency of Redshift. – Davos Apr 09 '19 at 11:25
  • 1
    I tried to understand the trend. Some of the users do understand the concepts but have such business requirements that they have to do an FTS or some other weird practices and you can't deny them, because, ahem, they are the business users. I tried to generalise things by setting system parameters but then I realised there are a lot of usecases, exceptions etc. It is then I decided to write a script accessing a metadata where all such exceptions and approvals are available. The script takes care of each and every user in a dynamic (personalised) way w/o fudging the system parameters :) – Yusuf Hassan Apr 09 '19 at 17:32
2

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"

Joe Harris
  • 13,671
  • 4
  • 47
  • 54
  • Do you have any recommended settings you use? I see the templates there are interesting. I feel like I would just have to guess at what is reasonable and then tweak it until the limits are right which seems time consuming. – Davos Apr 09 '19 at 11:28
  • 1
    You can use the view "wlm_qmr_rule_candidates.sql" from our Redshift Utils repo as a starting point. https://github.com/awslabs/amazon-redshift-utils/blob/a36138f34052e3a6d9072a4da9e46107da4eacd0/src/AdminScripts/wlm_qmr_rule_candidates.sql – Joe Harris Apr 15 '19 at 15:20
0

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

AlexYes
  • 4,088
  • 2
  • 15
  • 23
0

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

Mahen Nakar
  • 376
  • 2
  • 15