cloudwatch alarm for redshift queryduration


I have below cloudwatch alarm defined in CF template for altering me on queries running for 30 mins or more.

    Type: AWS::CloudWatch::Alarm  
      AlarmName: !Sub "awsredshift-${RSClusterName}-QueryDuration"  
      AlarmDescription: Redshift QueryDuration Alarm  
      Namespace: AWS/Redshift  
      MetricName: QueryDuration  
        - Name: ClusterIdentifier  
          Value: !Ref RSClusterName  
        - Name: latency  
          Value: long  
      ActionsEnabled: true  
        - !Ref TopicARN  
        - !Ref TopicARN  
      ComparisonOperator: GreaterThanOrEqualToThreshold  
      DatapointsToAlarm: 1  
      EvaluationPeriods: 1  
      Period: 300  
      Statistic: Average  
      Threshold: 1800000000  
      TreatMissingData: missing  

But its activating the alarms when there are no queries running that long, am I missing something?

Also is there any way to customize the alarms to put logic in them, I would like to get the SQL text of the query which is running longer. Is there any way to do this via cloudwatch alarms? If not whats the best way to do it - probably lambda?

asked 4 years ago634 views
1 Answer
Accepted Answer

Hi nmakb,

I think you're assuming the Redshift CloudWatch QueryDuration metric is something it's not. It's not a query level metric. It's a binned a QueryDuration dimensioned by ClusterIdentifier, latency (short, medium, long bins). Then in your CloudWatch Alarm you're aggregating that in an average over a 300 period.

Furthermore, I don't see a query id or anything related to join the individual metric data points data back to the source query.

I think the best you could do with the current QueryDuration metric would be to create a CloudWatch Alarm on the metric. trigger the Alarm on a theshold, set the action to a AWS Lambda to find queries in the alarm interval that were above the threshold. Then lastly do something with the result in the Lamdba, like trigger another SNS topic or send an email.

I hope this helps clarify what the CloudWatch QueryDuration metric is and isn't and provides you at least an idea of how you could build toward what you're looking to do.


answered 4 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions