pt-stalk dissection with bash

# Many of these are obvious and stupidly simple, but for your reference and copy-pasting pleasure…


# Mextify all (with workaround for pesky samples that contain multi-line RSA key that breaks pt-mext)
for i in $(ls $1/*-mysqladmin); do { (cat $i | sed -e '/\-\-\-\-\-BEGIN\ PUBLIC\ KEY/,+9d' | pt-mext -r -- cat -) > $i.mext && echo $i.mext;} done;

# Inspect given variable through multiple mext samples (revealing ;-))
grep Table_locks_waited *.mext |less -S

#Find contentious tables
grep -h waiting_table_lock *-lock-waits |sort|uniq -c |sort -nr

#Find the sample with the most lock waits
grep --count "LOCK WAIT" */*-innodb* |tr ":" " " |sort -nk2

#Aggregate semaphore waits
grep waited */*-innodb* |awk '{print $6, $8}'|sort |uniq -c |sort -nr

#Average gauge-type counters from mysqladmin ext samples:
for i in $(ls -1 *-mysqladmin); do { echo -n "$i --> "; grep Innodb_data_pending_reads $i|awk '{t+=$4} END {print (t/NR)}'; } done

#Totalize/Average counters from mext samples:
for i in $(ls -1 *.mext); do { echo -n "$i --> "; grep Com_select $i|awk '{$1=""; $2=""; print $0}'|tr " " "\n" |awk '{t+=$1} END {print ("total: ", t, ", avg: ", t/NR)}' } done;

#Find the samples with the heaviest loads; Useful when you have a ton of stalk samples and you are unsure which one to look at:
grep Threads_running *-mysqladmin |sort -grk4 |head -n10
grep "load average" *-top;

Aggregate threads by sample and state
#!/bin/bash
source=$1
f=0, l=0; grep -n ^TS $source |awk -F: '{print $1}'|while read n; do {
if [[ $f == 0 ]]; then { f=$n; continue; } fi;
l=$n;
tail -n +${f} $source |head -n $(($l - $f)) > $source-sample-$n;
f=$l;
} done;
for i in $(ls -1 ${source}-sample-*|sort -t"-" -k4 -h); do { echo $i; grep State $i|sort|uniq -c |sort -nr|head -n5; echo "============================================="; } done

#Totalize mutexes from mutex-statusX files:
f="2017_01_09_10_45_28-mutex-status1";
cat $f |awk -F"=" '{print $1}'|awk '{print $2}'|sort |uniq|egrep -v "Name|combined"|while read k; do {
echo -n $k;
fgrep "$k" $f | awk -F"=" '{ s+=$2 } END { print " --> ", s }';
} done |sort -nrk3
grep "MySQL thread" 2016_04_11_12_06_52-innodbstatus2 |awk '{$1=""; $2=""; $3=""; $4=""; $5=""; $6=""; $7=""; $8=""; $9=""; $10=""; $11=""; $12=""; $13=""; $14=""; print $0}'|sort|uniq -c|sort -nr
#
# Extracts thread state from SHOW ENGINE INNODB STATUS
#
# results:
# 898 sleeping
# 89 statistics
# 70 innobase_commit_low():trx_commit_for_mysql(-1)
# 55 Sending data
# 41 wsrep in pre-commit stage
# 37 update
# 37 Copying to tmp table
# 34
# 3 preparing
# 3 Opening tables
# 3 exit open_tables()
# 1 updating
# 1 removing tmp table
# 1 init
# 1 ha_commit_one_phase(-1)
 
 
grep "\-\-\-TRANSACTION" 2016_04_11_12_06_52-innodbstatus2 |awk '{$1=""; $2=""; $3=""; $4=""; $5=""; print $0 }'|sort |uniq -c |sort -nr
#
# Extract InnoDB thread status
# 998
# 97 index read
# 70 committing
# 55 starting index read
# 35 inserting
# 8 rollback
# 7 fetching rows
# 1 preparing
# 1 estimating records in index range
 
pt-diskstats --group-by=all 2016_11_09_14_44_23-diskstats --device=sda2 |grep -v in_prg |awk '{t+=$11} END {print t/NR}'

#This one is pt-query-digest but useful nonetheless: find top 20 queries from report A in report B:
grep -A22 "Profile" report-A|tail -n +3|awk '{print $3}'|while read fingerprint; do { grep $fingerprint report-B|head -n1; } done