Skip to content

Instantly share code, notes, and snippets.

@M1ke
Last active May 28, 2021 10:23
Show Gist options
  • Save M1ke/30af593eb40d36ad98d39f465ce4a830 to your computer and use it in GitHub Desktop.
Save M1ke/30af593eb40d36ad98d39f465ce4a830 to your computer and use it in GitHub Desktop.
Example queries for parsing mysql slow query logs

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

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)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment