CloudWatch Log Insights query examples for MySQL slow query log.

cloudwatch_log_insights_mysql_slow_query_examples.md

Filtering queries

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

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

Time series queries

Number of slow queries per hour

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

Number of slow queries per day

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

Averge of slow query duration per day

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

Max slow query duration per day

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

Stats queries

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

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

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

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

There is a typo on lock time regex named group:
Original: (<?lockTime>.*?)
Fixed: (?<lockTime>.*?)

Thank you so much for this!