Difference between revisions of "Top MySQL Slow Queries from log"

From James Dooley's Wiki
Jump to: navigation, search
(Created page with "==Overview== Sort the slow query log based on the query time and display the lines after it ==Script== ===Display 2 lines after Query_time=== <code>[bash,n] for i in $(grep -n Q...")
 
 
(4 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
[[Category:One Liners]]
 
==Overview==
 
==Overview==
 
Sort the slow query log based on the query time and display the lines after it
 
Sort the slow query log based on the query time and display the lines after it
  
 
==Script==
 
==Script==
−
===Display 2 lines after Query_time===
+
===Display 2 lines before/after Query_time===
−
<code>[bash,n]
+
<source lang='bash'>
−
for i in $(grep -n Query_time /home/mysql/slow.log | sort -k3 -nr | head | cut -d: -f1); do line=`sed -n \`echo $i\`p /home/mysql/slow.log`; grep -A2 "$line" slow.log;done
+
slowlog=`grep log-slow-queries /etc/my.cnf | cut -d'=' -f2`; \
−
</code>
+
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
 +
</source>
  
−
===Display 3 lines after Query_time | less===
+
===Display 2 lines before and 3 lines after Query_time | less===
−
<code>[bash,n]
+
<source lang='bash'>
−
for i in $(grep -n Query_time /home/mysql/slow.log | sort -k3 -nr | head | cut -d: -f1); do line=`sed -n \`echo $i\`p /home/mysql/slow.log`; grep -A3 "$line" slow.log;done | less
+
slowlog=`grep log-slow-queries /etc/my.cnf | cut -d'=' -f2`; \
−
</code>
+
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
 +
</source>

Latest revision as of 14:29, 25 March 2014

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
Retrieved from "http://wiki.jamesdooley.us/index.php?title=Top_MySQL_Slow_Queries_from_log&oldid=570"