CloudWatch Logs Insights User's Manual AWS Database RDS Advantages

Overview

As aws's log analysis and log search tool, all of the system's SQL queries into the database will be stored here, the tool will tell us: Look_time, Query_Time, Rows_sent, Row_examined of each query,
So it's great for searching slow queries, searching queries over time, testing SQL syntax, testing database performance issues.

Purpose of use

1. Investigate issues related to queries and databases
2. Search & find slow query topic fix performance

Commonly used queries

1. Find slow query with query_time greater than 0.250(s) and sort desc

parse @message "Query_time: * Lock_time: * Rows_sent: * Rows_examined: *\n*" as Query_time,Lock_time,Rows_sent,Rows_examined,q
| filter Query_time > 0.250
| sort Query_time desc

Example

2. Find slowest write queries

parse @message /Query_time: (?<queryTime>.*?) Lock_time: (?<lockTime>.*?) Rows_sent: (?<rowsSent>.*?) Rows_examined: (?<rowsExamined>.*?)\s(?<query>.*?)$/
| filter @message like /(?i)insert/
| sort queryTime desc
| limit 10

3. Find slowest read queries

parse @message /Query_time: (?<queryTime>.*?) Lock_time: (?<lockTime>.*?) Rows_sent: (?<rowsSent>.*?) Rows_examined: (?<rowsExamined>.*?)\s(?<query>.*?)$/
| filter @message like /(?i)select/
| sort queryTime desc
| limit 10

4. Number of slow queries per hour

parse @message /Query_time: (?<queryTime>.+?) /
| stats count() as count by bin(1h) as hour

5. Number of slow queries per day

parse @message /Query_time: (?<queryTime>.+?) /
| stats count() as count by bin(1d) as day

6. Averge of slow query duration per day

parse @message /Query_time: (?<queryTime>.+?) /
| stats avg(queryTime) as avg by bin(1d) as day

7. Max slow query duration per day

parse @message /Query_time: (?<queryTime>.+?) /
| stats max(queryTime) as max by bin(1d) as day

8. Summary stats for query time per hour

parse @message /Query_time: (?<queryTime>.+?) /
| stats count() as count, max(queryTime) as max, avg(queryTime) as avg by bin(1h) as hour

9. Summary stats of slow write queries by day

parse @message /Query_time: (?<queryTime>.+?) /
| filter @message like /(?i)insert/
| stats count() as count, max(queryTime) as max, avg(queryTime) as avg by bin(1d) as day

10. Summary stats of slow read queries by day

parse @message /Query_time: (?<queryTime>.+?) /
| filter @message like /(?i)select/
| stats count() as count, max(queryTime) as max, avg(queryTime) as avg by bin(1d) as day

11. Summary stats of slow write queries by table

filter @message like /(?i)insert/
| parse @message /(?i)# Query_time: (?<queryTime>.*?) [\s\S]*insert into '?(?<tableName>.*)'?\(?[\s\S]*/
| stats count() as count, max(queryTime) as max, avg(queryTime) as avg by tableName

ref:
https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CWL_QuerySyntax-examples.html

https://ap-northeast-1.console.aws.amazon.com/cloudwatch/home?region=ap-northeast-1#logsV2:logs-insights