Use mysqldumpslow to analyze mysql slow log

 1
 2
 3
 4
 5
 6
 7
 8

 # Enable slow query
 slow_query_log = on
 # slow query output to table and file
 log_output = TABLE,FILE
 # If the query time exceeds 2s, it is defined as a slow query
 long_query_time = 2
 # The path of the slow query file, if you do not specify an absolute path, for example, write it as I did below, by default it is in the data folder of mysql, such as /var/lib/mysql
 slow_query_log_file = slow.log

 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 twenty one
 twenty two
 twenty three
 twenty four
 25
 26
 27
 28
 29
 30
 31

 Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

 Parse and summarize the MySQL slow query log . Options are

 --verbose verbose
 --debug debug
 -- help write this text to standard output

 -v verbose
 -d debug
 -s ORDER what to sort by (aa, ae, al, ar, at, a, c, e, l, r, t), 'at' is default
 aa: average rows affected
 ae: aggregated rows examined
 al: average lock time
 ar: average rows sent
 at: average query time
 a: rows affected
 c: count
 e: rows examined
 l: lock time
 r: rows sent
 t: query time
 -r reverse the sort order (largest last instead of first)
 -t NUM just show the top n queries
 -a don 't abstract all numbers to N and strings to ' S '
 -n NUM abstract numbers with at least n digits within names
 -g PATTERN grep: only consider stmts that include this string
 -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
 default is ' * ', ie match all
 -i NAME name of server instance (if using mysql.server startup script)
 -l don't subtract lock time from total time