Example queries for parsing mysql slow query logs

cloudwatch-parse-slow-query.md

Using AWS RDS we can export default MySQL logs to CloudWatch. This can include the slow query log.

As standard, CloudWatch logs will just show the log message, which allows reading the log, but not doing useful analysis, such as finding the slowest queries over a time range, or the slow query which repeats the most often.

Using CloudWatch Insights we can write custom log search queries to try and extract more information. It starts with a parse step, where the "glob" parser can be used to take a single block log message and pull out individual data points:

PARSE @message '# Time: * *\n# User@Host: *[*] @ * Id: *\n# Query_time: * Lock_time: * Rows_sent: * Rows_examined: *\n*' as date,time,user,host,ip,id,duration,lock,rows,examined,query

From here we could improve the query using filters, groups or sorting to highlight slowest queries or most common slow queries

Tbc

datadog-parse-slow-query-log.md

Using the Datadog Lambda function via the Serverless Application Repository we can enable a trigger to send our Cloudwatch log group for the slow query log in to Datadog.

These are sent with source:rds so you can set up a custom pipeline.

In that pipeline, use the Grok parser with the following rule:

SlowLogRule ^(\# Time: (%{date("yyMMdd  H:mm:ss"):date}|%{date("yyMMdd HH:mm:ss"):date})\n+)?\# User@Host: %{notSpace: user1}\[%{notSpace: user2}\] @ (%{notSpace: host}| ) *\[%{regex("[0-9.]*"): ip}\]  Id:[\x20\t]+%{number: id}\n+\# Query_time: %{number: query_time} *Lock_time: %{number: lock_time} *Rows_sent: %{number: rows_sent} *Rows_examined: %{number: rows_examined}\n(SET timestamp=%{number: timestamp};\n+)?%{regex("[a-zA-Z].*"):query}.

(Credit for the Grok parser rule)