Differences

This shows you the differences between two versions of the page.

--- mysql:scripts [2009/01/06 13:06]
a Pretty InnoDB Buffer Pool Stats
+++ mysql:scripts [2009/05/25 00:35]
@@ Line 1: / Line 1: @@
-===== 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%)
-<code bash|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)%)"
-</code>
-===== Kill every MySQL SELECT older than X seconds =====
-<code bash|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
-</code>
-===== Sniff the MySQL traffic on the fly =====
-<code cli>
-# 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.=" $_";
-  }
-}'
-</code>