14

We're trying to do some server performance debugging and I would like to capture a snapshot of the queries being run on our MySQL server over a period of a couple minutes.

I'm familiar with MySQL's SHOW FULL PROCESSLIST, however I'd like to be able to run this via the command line so I can dump it to a file and post process it.

Is there a way to output this query to a file and have it run every second or so?

Is there a better way to capture all of the queries being run?

Note that I'm not interested in just the slow queries (I'm familiar with the slow query log).

ZygD
  • 337
  • 1
  • 3
  • 11
hafichuk
  • 762
  • 2
  • 5
  • 18
  • What OS are you running? This is extremely easy to do on linux, so I'm guessing windows? – phemmer Feb 10 '12 at 22:31
  • @Patrick Glad to hear it's easy on linux! Fire away... – hafichuk Feb 10 '12 at 22:33
  • 1
    Most queries will run and finish in much less than a second, and never show on your list. – Joel Coel Jan 29 '16 at 19:45
  • additionally longer queries will appear many times once for each loop, but for sure if you want you can just `echo show full processlist | mysql` or better `SELECT info FROM information_schema.processlist WHERE Command="Query" AND User!="root"` in a loop in bash. Add a couple of lines and you'll get the same query functionality than `innotop` or `pt-kill` – theist Dec 18 '17 at 15:33

9 Answers9

13

The most robust way would be to use the "general query log", which will capture all the queries: http://dev.mysql.com/doc/refman/5.1/en/query-log.html

You don't specify the MySQL server version, but if you have 5.1.12 or later you can enable and disable that with a global variable through SQL; see the documentation for details.

Daniel Pittman
  • 5,842
  • 1
  • 23
  • 20
  • Thanks @Daniel. We've turned this on, however there's no timestamp information in the log. Any idea how to get a timestamp in there? – hafichuk Feb 10 '12 at 23:32
  • Sorry, no, nothing other than pointing you to the manual. – Daniel Pittman Feb 11 '12 at 00:16
  • 2
    The slow query log with long_query_time=0 is a better option; it will still capture all queries. –  Feb 14 '12 at 16:49
  • +1 for the baron: the general query log does not include the performance metrics you need. There's also perl scripts bundled with MySQL server for analysing the slow query log (which remove literal values from predicates). But note that older versions of MySQL won't support a long_query_time of less than 1 second - if that's the case for you then upgrade - there's lots more performance enhancements in recent versions. – symcbean Jul 14 '12 at 23:49
10

I would use the slow query log. It captures all queries, not just those that are slow, if you set long_query_time = 0.

It also captures ALL queries, which is not true of the TCP-sniffing techniques mentioned here; those won't capture queries executed via a socket. Ditto for watching SHOW PROCESSLIST; you will miss fast-running queries.

If you want to capture queries via the processlist or via TCP traffic, I would suggest using Percona Toolkit's pt-query-digest. It can poll the processlist for you (and make sense out of the results, which is very hard to do if you're capturing a bunch of samples of it yourself), and it can interpret MySQL's TCP protocol, so you can grab some TCP traffic and analyze it. Of course, it's also the best query aggregator / profiler / reporter ever written, but you didn't say what you want to do with the queries after you capture them.

5

Sure:

mysqladmin -u root -p -i 1 --verbose processlist > /tmp/pl.out

Cheers

HTTP500
  • 4,833
  • 4
  • 23
  • 31
  • Ah...mysqladmin. Perfect, thanks @HTTP500 – hafichuk Feb 10 '12 at 22:40
  • 1
    This actually runs the processlist command every 1 second. Some very fast queries may still escape capture; if the load is made up of few large queries it will work, if it's made up of many small queries it might not. – LSerni Dec 07 '15 at 09:40
  • @Isemi, the OP asked for "every second or so". – HTTP500 Dec 07 '15 at 16:10
5

Try this command as root (or use sudo):

tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
  if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
    if (defined $q) { print "$q\n"; }
    $q=$_;
  } else {
    $_ =~ s/^[ \t]+//; $q.=" $_";
  }
}'

Found http://www.mysqlperformanceblog.com/2008/11/07/poor-mans-query-logging/

Rui Pedro Bernardino
  • 51
  • 1
  • 1
4

This might be a place for the Mysql Proxy. It basically allows you to capture (and manipulate) the queries being sent. A basic setup to intercept is pretty easy. Then just change your client config to point at the proxy so you can capture all the requests.

Jujhar Singh
  • 670
  • 6
  • 8
Zoredache
  • 130,897
  • 41
  • 276
  • 420
4

The terminal-based Wireshark program tshark may help:

tshark -T fields -R mysql.query -e mysql.query

sudo yum install wireshark will give you tshark on Amazon Linux and sudo apt-get install tshark will give you tshark on Ubuntu 14+

Jujhar Singh
  • 670
  • 6
  • 8
SoMoSparky
  • 161
  • 2
  • 5
3

I used 'Rui Pedro Bernardino's solution. Works great except I changed a couple things in the first line as detailed below...

tcpdump -i any -s 0 -l -vvv -w - dst port 3306 | strings | perl -e 'while(<>) { chomp; next if /^[^ ]+[ ]*$/;
    if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
    if (defined $q) { print "$q\n"; }
    $q=$_;
  } else {
    $_ =~ s/^[ \t]+//; $q.=" $_";
  }
}'
user1038090
  • 51
  • 4
0

Capture mysql tcp sessions using tcpdump, both queries and replies. You can analyze a dump for example using maatkit tools:

http://www.maatkit.org/doc/mk-tcp-model.html

http://www.maatkit.org/doc/mk-query-digest.html

Jannes
  • 200
  • 4
0

I was searching and searching and finally I landed at MONyog for monitoring all the queries at real-time that are executed in the mysql server.The only thing to be noted is "Performance_schema" and "statements_digest" table is to be enabled and Performance_schema is available with MySQL 5.6.14 and above.

Mathew
  • 101