You can use a command called mysqldumpslow to analyze the slow login in MySQL. To you use it, you must first enable slow query logging, then generate a slow query and finally look at the slow query log.
Below is a brief listing of the different arguments of mysqldumpslow:
This program parses and summarizes a ‘slow query log’.
-v verbose
-d debug
-s=WORD
what to sort by (t, at, l, al, r, ar etc)
-r reverse the sort order (largest last instead of first)
-t=NUMBER
just show the top n queries
-a don’t abstract all numbers to N and strings to ‘S’
-n=NUMBER
abstract numbers with at least n digits within names
-g=WORD
grep: only consider stmts that include this string
-h=WORD
hostname of db server for *-slow.log filename (can be wildcard)
-i=WORD
name of server instance (if using mysql.server startup script)
-l don’t subtract lock time from total time
Some examples of commands used to analyze the slow log are below.
mysqldumpslow -t 10 /var/lib/mysql/mysql-slow.log.1 > /tmp/top_ten_slow_queries.txt
The command above will list the top 10 slow queries in the query log and output the file to /tmp/top_ten_slow_queries.txt.
mysqldumpslow -a -s c -t 10 /var/lib/mysql/mysql-slow.log.1 > /tmp/top_ten_repeating_slow_queries.txt
This command will show you the top 10 repeating slow queries and output to /tmp/top_ten_repeating_slow_queries.txt.
invest
December 25, 2010 at 9:18 am
Cool writing work, thx for your effort.