Mysql – How to capture the queries run on MySQL server

MySQL

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

Best Answer

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.

Related Solutions

Mysql – tool to filter the MySQL slow query log based on database

you can use this little awk script to do the job:

awk '{ if ($0 ~ /use \[.*\];/) { if ($2 ~ /db1/) { found = 1; } else { found = 0; }} if (found == 1) { print $0; }}' <mysqllogfile>

just replace db1 with the database name you are searching for. when you take your example from above, this script will give you:

use [db1];
SELECT ...
# Time: 090226 11:17:34
# User@Host: user1[user1] @ host [10.0.0.3]
# Query_time: 12  Lock_time: 0  Rows_sent: 0  Rows_examined: 4042560
SELECT ...
# Time: 090226 12:32:40
# User@Host: user2[user2] @ host [10.0.0.3]
# Query_time: 8  Lock_time: 0  Rows_sent: 123390  Rows_examined: 812841

i don't know what is your operation system, but awk/gawk is available for multiple os.

Mysql – Can the thesql slow query log show milliseconds

try this: Slow Query Log Extended Statistics

Best Answer

Related Solutions

Mysql – tool to filter the MySQL slow query log based on database

Mysql – Can the thesql slow query log show milliseconds

Related Topic