A MySQL slowlog parser based on winnow

[soulstompp]

I have spent far too many work days grepping and reading through MySQL's slow logs. I have just released v 0.3.0 of mysql-slowlog-parser which is a log parser using winnow to produce a full representation of the data buried in these logs, including a full AST of the SQL query that was executed, easily extractable.

In most cases, I am not looking at these logs like a DBA and so many of the great tools out there, like Percona Grafana Dashboard, don't tend to fit what I need most of the time, since my use case is pretty different from the DBAs it was intended for.

My workflow is generally closer to this:

1. Set a dev or state environment to log performance of every query in the slowlog
2. Run regression testing and save a copy of the log during just this run somewhere.
3. Follow a specific request id through provided in the comments the caller included with the query.
4. Normalize the queries (ignore values passed to the query)
5. Correlate queries run for the request based on the tables they are accessing.
6. Review where in the code the correlated queries happened and determine which query correlations are feasible without large disruptive code changes.
7. Attempt to balance feasibility and effort with gains.

With this parser, I am able to jump right to step 5 and can concentrate on the remaining steps. This is because the parser helps with the following:

• Parses the initial comment of the query to find request id, caller and method.
• Parses the query in the log entry and extracts the query type and all tables accessed in the query.
• Optionally normalizes queries by removing all values passed to a query and replaces them with a placeholder.

Removing these tedious steps has gone a long way toward quickly finding how to optimize how an application interacts with an RDBMS. I have managed up to 6x performance improvements with this approach without any major architectural changes. I hope with this parser (and some upcoming tools I have planned using this parser) can help others to find similar wins with dramatically less effort.

I plan on future projects that will help to automate all of these steps further, but all of it required being able to fully access the data in these logs. MySQLs slowlog format is very loosely structured and more intended for human eyes, so I was happy to have winnow at hand to put it all together.