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:

  • 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.