Kevin Burton’s NEW FeedBlog

The output from SHOW INNODB STATUS isn’t very pretty or easy to use. Seriously, other than Heikki who thinks in 16k block sizes? :-)

A few lines of bash magic fixes this problem.

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%)

Here’s what the code looks like:

#!/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

}

# don't 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)%)"