CloudWatch Logs Insights User's Manual AWS Database RDS Advantages
Tags: aws, cloudwatch, rds
Published: 2024-02-05 07:48:36

**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


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dlx632rjmjlex2net88f.png)



**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