Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
mysql:scripts [2008/11/21 18:01] a |
mysql:scripts [2009/05/25 00:35] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ===== Pretty InnoDB Buffer Pool Stats ===== | ||
+ | Now I can quickly see buffer pool stats in the following format: | ||
+ | |||
+ | | ||
+ | used: 16563568640 bytes (16G) (59.00%) | ||
+ | | ||
+ | |||
+ | <code bash|f innodb-status.sh> | ||
+ | #!/bin/sh | ||
+ | |||
+ | # Pretty print InnoDB buffer stats. | ||
+ | # | ||
+ | # SHOW INNODB STATUS looks like: | ||
+ | # | ||
+ | # Buffer pool size | ||
+ | # Free buffers | ||
+ | # Database pages | ||
+ | # 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" | ||
+ | |||
+ | #echo $stats | ||
+ | set -o noglob | ||
+ | |||
+ | buffer_pool_size=$(echo $stats | grep -E ' | ||
+ | buffer_pool_size=$(expr $buffer_pool_size * 16384) | ||
+ | |||
+ | free_pool_size=$(echo $stats | grep -E '^Free buffers' | ||
+ | free_pool_size=$(expr $free_pool_size * 16384) | ||
+ | |||
+ | modified_db_pages=$(echo $stats | grep -E ' | ||
+ | modified_db_pages=$(expr $modified_db_pages * 16384) | ||
+ | |||
+ | used=$(expr $buffer_pool_size - $free_pool_size) | ||
+ | |||
+ | echo " | ||
+ | echo "used: $used bytes ($(pp $used)) ($(perc $used $buffer_pool_size)%)" | ||
+ | |||
+ | echo -n " | ||
+ | echo -n "($(pp $modified_db_pages)) " | ||
+ | echo " ($(perc $modified_db_pages $buffer_pool_size)%)" | ||
+ | </ | ||
+ | |||
===== Kill every MySQL SELECT older than X seconds ===== | ===== Kill every MySQL SELECT older than X seconds ===== | ||
<code bash|f kill.sh> | <code bash|f kill.sh> | ||
Line 16: | Line 87: | ||
done | done | ||
</ | </ | ||
+ | |||
===== Sniff the MySQL traffic on the fly ===== | ===== Sniff the MySQL traffic on the fly ===== | ||
Line 30: | Line 102: | ||
</ | </ | ||
+ | ===== RESET SLAVE ===== | ||
+ | <code bash|f reset-mysql-slave.sh> | ||
+ | #!/bin/bash | ||
+ | |||
+ | HOST=$1 | ||
+ | RPASS=$(< | ||
+ | |||
+ | IFS=" | ||
+ | " | ||
+ | |||
+ | mysql -h $HOST -e "STOP SLAVE" | ||
+ | |||
+ | for line in $(mysql -e "SHOW SLAVE STATUS" | ||
+ | do | ||
+ | key=${line%: | ||
+ | key=${key// | ||
+ | data=${line# | ||
+ | data=${data// | ||
+ | |||
+ | 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"; | ||
+ | fi | ||
+ | |||
+ | mysql -f -h $HOST -e " | ||
+ | RESET SLAVE; | ||
+ | CHANGE MASTER TO | ||
+ | MASTER_HOST=' | ||
+ | MASTER_USER=' | ||
+ | MASTER_PASSWORD=' | ||
+ | MASTER_LOG_FILE=' | ||
+ | MASTER_LOG_POS=$LOGPOS ; | ||
+ | START SLAVE | ||
+ | " | ||
+ | </ | ||