Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- # 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement