With MySQL 5.1, getting and analyzing all the queries hitting the database is very easy even if you don’t have access to the MySQL enterprise Monitor. You can either activate the general log or, my favourite option, activate the slow query log with a sub millisecond long query time. Long query time below one second is an option introduced in MySQL 5.1. In order to catch all the queries, you will also need to disable the query cache. The main advantages of the slow query log over the general log is that you have access to more information like the query time, the lock time, the number of rows examined and the number of rows sent back. All the options surrounding the slow query log in MySQL 5.1 are dynamic so it is easy to turn the logging ON and OFF. Once you have the queries, tools, like mysqlsla, can be used to anlyzed the queries and group them by type.
All these tips are nice if you are using MySQL 5.1 but what if your database is using 5.0 and cannot be easily restarted. With MySQL 5.0, the smallest long_query_time you can used is 1 second so, even if the slow query log is ON, it is not very useful to log all the queries. To activate the general query log requires a restart so, if you cannot restart the database, it is not option. The question remains, how can we get the queries. The response is with with the use tcpdump and tshark along with some file manipulation. Here are the steps:
1. Capturing the MySQL traffic
tcpdump -i eth0 port 3306 -s 1500 -w tcpdump.out
Of course, this is useless with a Unix socket file.
2. Extracting the queries
With the tcpdump.out file, you can now extrat the queries with tshark. The following command will extract all the queries:
tshark -r tcpdump.out -d tcp.port==3306,mysql -T fields -e mysql.query > query_log.out
I prefer to hint tshark as which tcp to use for mysql because I often end up with installations using non-standard port number and this way
I remember easily. The output file content looks like:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT mi.id, mi.size, mi.blob_digest FROM mboxgroup10.mail_item AS mi WHERE mailbox_id = ? AND folder_id = ? AND type IN (5) A
ND NOT flags & 128
The last step is to remove the blank lines and redundant SQL.
cat query_log.out | grep -v “^$” | grep -v “^commit” | grep -v “^SET autocommit” | grep -v “^rollback” > query_log_no_blank.out
and you are done. You can use any tool you want to analyze the queries, my favourite one is mysqlsla although, for this specific case, you will need to hack a bit the code since, in raw format, mysqlsql expect “;\n” as end of line. Simply find the line in parse_raw_logs:
$/ = “;n”;
and change it to:
$/ = “n”;