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.
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