Differences

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

--- mysql:scripts [2008/11/21 18:01]
a
+++ mysql:scripts [2009/01/06 13:06]
a Pretty InnoDB Buffer Pool Stats
@@ 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>