Logging all the queries with MySQL 5.0

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:

commit

rollback

SET autocommit=1

SET autocommit=0

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

commit

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”;

About Yves Trudeau

I work as a senior consultant in the MySQL professional services team at Sun. My main areas of expertise are DRBD/Heartbeat and NDB Cluster. I am also involved in the WaffleGrid project.
This entry was posted in mysql, performance, script, yves. Bookmark the permalink.