Wednesday, 1 April 2009

Mysqldumpslow -How to

Note: In case if you haven't enabled slow log and are using 5.0.x then add following line into my.cnf under [mysqld] and restart mysql server.
####### 5.0.x
long_query_time=5 # capture slowest slow queries first

# if you have installed 5.1.x then follow following steps
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.02 sec)
mysql> set global long_query_time=5;
Query OK, 0 rows affected (0.00 sec)

You need to have perl installed on your system in order to run mysqldumpslow. If you have perl installed but in different location from /usr/local/bin/perl, you can either edit the mysqldumpslow
script to point to the right location of the perl binary or you can create symbolic link /usr/local /bin/perl that points to the real location of the perl binary
mysqldumpslow -s c -t 10 /path-to-slowlog/slowlog.log

If the log file is big, mysqldumpslow might put some load on your cpu so I would suggest that you copy the log file on some other box and run mysqldumpslow there, just to avoid cpu load on your server.

In order to parse only 200 last lines of the file, you will have to do something like this:

tail --lines=200 /data/data/eus3300005-slow.log | mysqldumpslow -t20 -

(tail --lines=200 will get only last 200 lines and the last "-" parameter
for mysqldumpslow tell's it to read the log from stdin)

For solaris 10, please use:

tail -200 /data/data/eus3300005-slow.log | mysqldumpslow -t20 -

1 comment:

  1. to analyze in windows, you could download this tools, it wrapped the perl and mysqldumpslow in nice interface . You also can download and modified the source code at