Pretty InnoDB Buffer Pool Stats
Now I can quickly see buffer pool stats in the following format:
buffer pool size: 27999076352 bytes (27G) used: 16563568640 bytes (16G) (59.00%) modified db pages: 4747952128 bytes (4G) (16.00%)
f innodb-status.sh
#!/bin/sh # Pretty print InnoDB buffer stats. # # SHOW INNODB STATUS looks like: # # Buffer pool size 1708928 # Free buffers 1142066 # Database pages 565676 # Modified db pages 123467 pp() { value=$1 if [ $value -gt 1000000000 ]; then value=$(expr $value / 1000000000)G elif [ $value -gt 1000000 ]; then value=$(expr $value / 1000000)M elif [ $value -gt 1000 ]; then value=$(expr $value / 1000)K fi echo $value } perc() { nr=$1 total=$2 echo "scale = 2; ($nr / $total ) * 100" | bc } # donnot let bash screw up multiline parsing (I hate this bug) IFS= stats=$(echo "SHOW ENGINE INNODB STATUS\G" | mysql -N) #echo $stats set -o noglob buffer_pool_size=$(echo $stats | grep -E '^Buffer pool size' | grep -Eo '[0-9]+$') buffer_pool_size=$(expr $buffer_pool_size * 16384) free_pool_size=$(echo $stats | grep -E '^Free buffers' | grep -Eo '[0-9]+$') free_pool_size=$(expr $free_pool_size * 16384) modified_db_pages=$(echo $stats | grep -E '^Modified db pages' | grep -Eo '[0-9]+$') modified_db_pages=$(expr $modified_db_pages * 16384) used=$(expr $buffer_pool_size - $free_pool_size) echo "buffer pool size: $buffer_pool_size bytes ($(pp $buffer_pool_size))" echo "used: $used bytes ($(pp $used)) ($(perc $used $buffer_pool_size)%)" echo -n "modified db pages: $modified_db_pages bytes " echo -n "($(pp $modified_db_pages)) " echo " ($(perc $modified_db_pages $buffer_pool_size)%)"
Kill every MySQL SELECT older than X seconds
f kill.sh
#!/bin/bash SEC=$1 IFS=’|’ if [[ $SEC -lt 1 ]]; then echo “Usage: $0 SECONDS” exit 1 fi mysqladmin proc -v|grep Query|grep -Evi “delete|update|insert|alter table” |while read dummy qid qusr qhost qdb qstat qsec qstat2 query do if [ $qsec -gt $SEC ]; then echo “Killing query $qid…” mysqladmin kill $qid fi done
Sniff the MySQL traffic on the fly
# tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e ' while(<>) { chomp; next if /^[^ ]+[ ]*$/; if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) { if (defined $q) { print "$q\n"; } $q=$_; } else { $_ =~ s/^[ \t]+//; $q.=" $_"; } }'
RESET SLAVE
f reset-mysql-slave.sh
#!/bin/bash HOST=$1 RPASS=$(<passwordfile) IFS=" " mysql -h $HOST -e "STOP SLAVE" for line in $(mysql -e "SHOW SLAVE STATUS" -E -h $HOST) do key=${line%:*} # Split off what goes before : key=${key// } # And trim spaces data=${line#*:} # Split off what goes after : data=${data// } # And trim spaces yet again!!! case $key in Exec_master_log_pos) LOGPOS=$data;; Relay_Master_Log_File) LOGFILE=$data;; Master_Host) RHOST=$data;; Master_User) RUSER=$data;; esac done if [ -z $LOGPOS -o -z $LOGFILE ]; then echo "OMG FAIL NO POSITIONS KNOWN"; exit; fi mysql -f -h $HOST -e " RESET SLAVE; CHANGE MASTER TO MASTER_HOST='$RHOST', MASTER_USER='$RUSER', MASTER_PASSWORD='$RPASS', MASTER_LOG_FILE='$LOGFILE', MASTER_LOG_POS=$LOGPOS ; START SLAVE "