How to create alerts based on Postgres slow queries?

Question

I have my Aurora Postgres cluster configured to log slow queries (log_min_duration_statement: 1000). How can I setup a Cloudwatch alert to notify me when a query's average speed exceeds this?

Or do I need to use the slow logging at all? I see that RDS Performance Insights provides a "Top SQL" tab which includes an "Avg latency (ms)/call" measurement. I presume that this is the data I want to monitor. Can I setup a Cloudwatch alert based on that?

Accepted Answer

Hi, to achieve your goal, you have to create a metric filter from the CloudWatch log events corresponding to the slow queries.
You will be then able to extract the value corresponding to the duration of your query from the log event message.
This duration will be added to your cloudwatch metric tracking those events.
You can then fire a CloudWatch alarm when the average is above your target.

In you case, you may want to in fact distinguish your different queries in different metrics to have more granular tracking.

Links:
1. Amazon RDS : turn on query logging for PostgreSQL https://repost.aws/knowledge-center/rds-postgresql-query-logging
2. Creating metrics from log events using filters:  https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/MonitoringLogData.html
3. Filter and pattern syntax to extract value from message: https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/FilterAndPatternSyntax.html#extract-log-event-values
4. https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/AlarmThatSendsEmail.html

Best,

Didier

Answer

Hi,

Look also into event notifications: https://repost.aws/knowledge-center/create-rds-event-subscription

How to create alerts based on Postgres slow queries?

Relevant content