Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
What OS are you running? This is extremely easy to do on linux, so I'm guessing windows? –  Patrick Feb 10 '12 at 22:31
    
@Patrick Glad to hear it's easy on linux! Fire away... –  hafichuk Feb 10 '12 at 22:33

7 Answers 7

up vote 6 down vote accepted

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.

share|improve this answer

Sure:

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

Cheers

share|improve this answer
    
Ah...mysqladmin. Perfect, thanks @HTTP500 –  hafichuk Feb 10 '12 at 22:40

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.

share|improve this answer

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.

share|improve this answer
    
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 0:16
2  
The slow query log with long_query_time=0 is a better option; it will still capture all queries. –  Baron Schwartz 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

The terminal-based Wireshark program tshark may help:

tshark -T fields -R mysql.query -e mysql.query
share|improve this answer

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

share|improve this answer

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/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.