mk-query-digest Tips – Showing all hosts & users

The Maatkit tools provide a suite of additional MySQL commands. There is one command I use constantly and that is mk-query-digest.

Unfortunately the documentation does leave a lot to be desired for usability. While throughout, it is a man page and not a user guide. Several of us have discussed writing better documentation however it’s always a matter of time. I have however learned a number of tips and I’d like to share them in smaller digests.

The first is showing additional display. Maatkit works on truncating per line output to a reasonable length of 73 characters?

One of those lines is the list of hosts that connected to MySQL for a query, for example.

# Hosts                  4 192.168.40... (2), 192.168.40... (2)... 2 more
# Hosts                  3 99.99.245.14 (12), 999.106.206.167 (6)... 1 more

The problem is I want to know what that 1 more is so I can gather a complete list of IP addresses that connect to this server. You do that with the –show-all=host argument.

Without

$ cat external.tcpdump | ./mk-query-digest --type tcpdump | grep Hosts | uniq -c
#
      1 # Hosts                  3 99.99.245.14 (12), 999.106.206.167 (6)... 1 more
      1 # Hosts                  1 99.99.139.140

With

$ cat external.tcpdump | ./mk-query-digest --type tcpdump --show-all=host | grep Hosts | uniq -c
      1 # Hosts                  3 99.99.245.14 (12), 999.106.206.167 (6), 99.99.139.140 (2)
      1 # Hosts                  1 99.99.139.140

You can apply the same principle to the Users as well with –show-all=user

$ cat external.tcpdump | ./mk-query-digest --type tcpdump  --show-all=user | grep Users | uniq -c
      1 # Users                  2 xxx (13), phpmysqlmo... (5)
     49 # Users                  1  xxx

The problem is a still gett a truncation of the name ‘phpmysqlmo…’ That’s the one thing I’m trying to uncover, because that IP and usernme are not valid permissions for this system.

Tags: , ,

One Response to “mk-query-digest Tips – Showing all hosts & users”

  1. Gavin Towey says:

    agreed, this is a great tool. Its defaults show you what you want to see initially, and there’s a lot of powerful features.

    One case I use it for is when I’m looking at a aggregate list of queries and see a histogram like this:
    # 1us
    # 10us
    # 100us
    # 1ms ##
    # 10ms ###############
    # 100ms ################################################################
    # 1s #################
    # 10s+

    I want to see those queries which took longer than 1 second:

    mk-query-digest –filter ‘ $event->{arg} =~ m// && $event->{Query_time} > 1′ –noreport –print mysql-slow.log

    I haven’t checked more recent versions, but I don’t think there was a way to filter based on fingerprint. If so that would be much better than having to regex on the actual query string. In this case $event->{arg} is the actual query