innodb_status_analysis.sh

## Analyze InnoDB status
grep -h "lock on" log | cut -d "-" -f2- | sort | uniq -c | sort -nr > lock_on.txt

grep -h "Mutex at" log | cut -d "," -f 1 | sort | uniq -c | sort -nr > mutex_at.txt

grep -h "has waited at " log | awk '{print $6" "$7" "$8}' | sort | uniq -c | sort -nr > has_waited_at.txt

grep -h "Last time write locked " log | sort | uniq -c | sort -nr > last_time_write_locked.txt

grep -A 2 "\-\-\-TRANSACTION" log | grep COMMIT | cut -d "," -f 1,2,3 | sort | uniq -c | sort -nr  > commit_statements.txt


## Tables involved most in lock_waits
mysql -uroot -e "show engine innodb status\G" | grep "FOR THIS LOCK TO BE GRANTED" -A 1 | grep "RECORD LOCKS" | awk '{print $16}' | sort | uniq -c | sort -nr

## App servers affected by lock_waits
mysql -uroot -e "show engine innodb status\G" | grep "LOCK WAIT" -A 1 | grep "MySQL thread id" | awk '{print $12}' | sort | uniq -c | sort -nr

## Tables seeing most locking contention
mysql -uroot -e "show engine innodb status\G" | grep "TABLE LOCK" | awk '{print $4}' | sort | uniq -c | sort -nr

## map thread id in SEMAPHORES section to transactions in TRANSACTION section
#!/bin/bash

for thd_id in $(grep "\-\-Thread" engine_status_20140715.txt | grep
"dict0dict" | awk '{print $2}')
do
        os_thd_id=$(mysql -NB -e "select lower(hex(${thd_id}))")

        grep "OS thread handle 0x${os_thd_id}" engine_status_20140715.txt -A 1
done