DEV Community

Tai Ha for AWS Community ASEAN

Posted on

1 1 1 1 1

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

Image description

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

Image of Datadog

Learn how to monitor AWS container environments at scale

In this eBook, Datadog and AWS share insights into the changing state of containers in the cloud and explore why orchestration technologies are an essential part of managing ever-changing containerized workloads.

Download the eBook

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more