How To Enable Mysql Slow Query Log

In my.cnf

slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slowquery.log
long_query_time = 1

In SSH

mysqldumpslow /var/lib/mysql/slowquery.log

Following will show top 5 query which returned maximum rows. It can find queries where you missed LIMIT clause. A common performance killer!

mysqldumpslow -a -s r -t 5 /var/lib/mysql/slowquery.log

Following will sort output by count i.e. number of times query found in slow-log. Most frequency queries sometimes turned out to be unexpected queries!

mysqldumpslow -a -s c -t 5 /var/lib/mysql/slowquery.log

Leave a Reply

Your email address will not be published. Required fields are marked *