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.