Difference between revisions of "Top MySQL Slow Queries from log"
From James Dooley's Wiki
| Line 5: | Line 5: | ||
==Script== | ==Script== | ||
===Display 2 lines before/after Query_time=== | ===Display 2 lines before/after Query_time=== | ||
| − | < | + | <source lang='bash'> |
slowlog=`grep log-slow-queries /etc/my.cnf | cut -d'=' -f2`; \ | slowlog=`grep log-slow-queries /etc/my.cnf | cut -d'=' -f2`; \ | ||
if [ -e "$slowlog" ]; then \ | if [ -e "$slowlog" ]; then \ | ||
for i in $(grep -n Query_time $slowlog | sort -k3 -nr | head | cut -d: -f1); do line=`sed -n \`echo $i\`p $slowlog`; grep -B2 -A2 "$line" $slowlog;done; \ | for i in $(grep -n Query_time $slowlog | sort -k3 -nr | head | cut -d: -f1); do line=`sed -n \`echo $i\`p $slowlog`; grep -B2 -A2 "$line" $slowlog;done; \ | ||
fi | fi | ||
| − | </ | + | </source> |
===Display 2 lines before and 3 lines after Query_time | less=== | ===Display 2 lines before and 3 lines after Query_time | less=== | ||
| − | < | + | <source lang='bash'> |
slowlog=`grep log-slow-queries /etc/my.cnf | cut -d'=' -f2`; \ | slowlog=`grep log-slow-queries /etc/my.cnf | cut -d'=' -f2`; \ | ||
if [ -e "$slowlog" ]; then \ | if [ -e "$slowlog" ]; then \ | ||
for i in $(grep -n Query_time $slowlog | sort -k3 -nr | head | cut -d: -f1); do line=`sed -n \`echo $i\`p $slowlog`; grep -B2 -A3 "$line" $slowlog;done \ | for i in $(grep -n Query_time $slowlog | sort -k3 -nr | head | cut -d: -f1); do line=`sed -n \`echo $i\`p $slowlog`; grep -B2 -A3 "$line" $slowlog;done \ | ||
fi | less | fi | less | ||
| − | </ | + | </source> |
Latest revision as of 14:29, 25 March 2014
Contents
Overview
Sort the slow query log based on the query time and display the lines after it
Script
Display 2 lines before/after Query_time
slowlog=`grep log-slow-queries /etc/my.cnf | cut -d'=' -f2`; \
if [ -e "$slowlog" ]; then \
for i in $(grep -n Query_time $slowlog | sort -k3 -nr | head | cut -d: -f1); do line=`sed -n \`echo $i\`p $slowlog`; grep -B2 -A2 "$line" $slowlog;done; \
fi
Display 2 lines before and 3 lines after Query_time | less
slowlog=`grep log-slow-queries /etc/my.cnf | cut -d'=' -f2`; \
if [ -e "$slowlog" ]; then \
for i in $(grep -n Query_time $slowlog | sort -k3 -nr | head | cut -d: -f1); do line=`sed -n \`echo $i\`p $slowlog`; grep -B2 -A3 "$line" $slowlog;done \
fi | less
