MySQL slow log is extremely useful feature of MySQL server. But its investigation can be very painful. Mainly due to the usually huge size of it. Investigation using command line tools is usually the quickest possibility. So here are few useful commands:
- find longest query times:
cat yourhostname-slow.log |grep "# Query_time:"|grep -v "# Query_time: 0."|tr -s ' '|cut -d' ' -f3|sort -hr|head -n10
you will see output like this:
11002.086175 7884.787151 5135.913643 4663.030323 ....
- show queries for previously found longest query times:
cat yourhostname-slow.log |grep "# Query_time: 11002.086175" -B2 -A10
will show full header of given query and 10 following lines. So you can either see only few lines from the query or already beginning of following query.