MySQL: Analyze slow query log using mysqldumpslow

First enable slow query logging, then generate a slow query and finally look at the slow query log.

This program parses and summarizes a ‘slow query log’.
-v verbose
-d debug
what to sort by (t, at, l, al, r, ar etc)
-r reverse the sort order (largest last instead of first)
just show the top n queries
-a don’t abstract all numbers to N and strings to ‘S’
abstract numbers with at least n digits within names
grep: only consider stmts that include this string
hostname of db server for *-slow.log filename (can be wildcard)
name of server instance (if using mysql.server startup script)
-l don’t subtract lock time from total time

1. mysqldumpslow -t 10 /var/lib/mysql/mysql-slow.log.1 > /tmp/top_ten_slow_query.txt
It will put top ten slow query in file /tmp/top_ten_slow_query.txt
2. mysqldumpslow -a -s c -t 10 /var/lib/mysql/mysql-slow.log.1 > /tmp/top_ten_repeat_slow_query.txt
It will put top ten repeat slow query in file top_ten_repeat_slow_query.txt

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: