MK-Query-Digest

From OpenSQLCamp

Overview

Before Mk-Query-Digest there were other log parsers, but none of them had tests for different log formats (microsecond patch, etc.

Not just for slow queries, but will aggregate and analyze pretty much any group of queries. By default its used for the slow query log, but it can do other stuff too. You can pass multiple files at once to get an aggregate log.

Example: The Maatkit source has a 'samples' directory with sample data to run against mk-query-digest.

By default it ranks queries by aggregated query time.

It can pickup on virtually any (custom) attributes per query that the script finds.

The parser is smart enough to abstract out values in the WHERE clause. IE. SELECT * FROM users WHERE userid = "1" is read the same as SELECT * FROM users WHERE userid = "2". The parser will also abstract out queries from tables with like prefixes / suffixes, ie. table1_users and table2_users

Results: Initial Metadata First line tells you about the perfomance of the tool itself followed by a table describing an overview of the report.

Next is a section for each 'fingerprinted' query. (A query that has been identified with values abstracted out). Each section has columns for %, total, min, max, avg, 95%, stddev, median for the following analysed values of Count, Execution time, Lock time, Rows sent, and Rows examined.

At the end of the report is a summary showing response time, calls, and response time per call. Tip: look here first for queries that are worth spending time on optimizing.

Questions

Q: Can we filter? A: "You can filter by anything" -- It helps if you know Perl. Use the command like --filter 'PERL_CODE'

Ex: if you want to filter for query times greater than 0.07s it looks like the following:

mk-query-digest --filter '$event->{Query_time} > 0.07' slow_query.log

To see what properties are available on each event, do:

mk-query-digest --filter 'print Dumper $event' slow_query.log

For more complex filtering, pass a perl file to the argument:

mk-query-digest --filter myPerlModule.pl slow_query.log

Q: Storing results in a table A: Use the --create-review-history-table argument

using this option will store info about the fingerprinted queries so that the next time you run the anayzer it won't spend time on queries already seen.

Q: How do we configure? A: There are 4 coniguration files that the tool looks for by default: 1 maatkit global conf, 1 global mk-query-digest, 1 user specific maatkit, and 1 user specific mk-query-digest

You can pass your own config file using the --config option to override values.

Q: How can we use tcpdump to sniff traffic? A: By default mk-query-digest parses the slow-query log but it can be used in a variety of ways. The tool understands the mysql protocol. This can also be used to sniff memcached traffic. HTTP is coming soon. Basically any request / response cycle should be possible.