Once again, I was unable to attend all of the sessions I wanted to at this year’s User Converence, but I was happy to make it to Bob Burgess‘ talk on bash scripting with mysql. The slides and examples aren’t up yet, but when they are (which may be as you read this, check the last link), they would probably also be a great tutorial.

So, I got bore^D^D^D^D inspired later that day to put some of the practices into use, and worked up a script to run mysqlslap in various ways against a server, and then added a couple funcitons to try it out on each storage engine. The script is below in its entirety – bash scripters, please be kind in your comments. No, I didn’t write all this just for the pun in the subject. But I’m not above that.

The result?

Why don’t I use more BLACKHOLE tables? They are blazing fast!

 My results (on my lenovo T61, Fedora 10):

SLAP Base values:
 50 simultaneous connections ||  10 runs through
 Writes : 1000, 500 unique (Commit every 500) || Queries: 1000, 200 unique
 Schema: 4 character columns, 8 numeric with auto-increment PK and 10 secondary indexes
For InnoDB: 0.389 Average, 0.299 Min, 0.651 Max
For MyISAM: 0.364 Average, 0.355 Min, 0.377 Max
For BLACKHOLE: 0.137 Average, 0.124 Min, 0.147 Max
For CSV: n/a Average, n/a Min, n/a Max
For MEMORY: 0.375 Average, 0.363 Min, 0.444 Max
For ARCHIVE: n/a Average, n/a Min, n/a Max
For MRG_MYISAM: n/a Average, n/a Min, n/a Max

The "n/a" ones are tables that, generally for obvious reasons, couldn’t do the slap. My error handling needs work.

There are some expected trends that are good to validate – InnoDB improves with more concurrency (in a relative sense), MEMORY has remarkably little fluxuation in response time, things like that. But the marketing guys really have to capitalize on those BLACKHOLE numbers :-)

#!/bin/bash
shopt -s -o nounset
printf "Enter root pwd: "
read -s PASSWORD
# get the list of active engines from MySQL
ENGINES=`mysql -uroot -p$PASSWORD -B -N -e "SELECT ENGINE from ENGINES WHERE SUPPORT<>’NO’" INFORMATION_SCHEMA`
#for e in $ENGINES; do
#    printf "\nFound engines: %s" $e
#done
printf "\nStarting test at %s \n" `date +%H:%M:%S`
# default initial settings
ITERATIONS=10
CONCURRENCY=50
COMMIT=500
WRITES=1000
let "UNIQUE_WRITES=$WRITES/2"
QUERIES=1000
let "UNIQUE_QUERIES=$QUERIES/5"
LOAD_TYPE=mixed
CHARS=4
INTS=8
INDX=10
SLAP="mysqlslap -u root -p$PASSWORD -h 127.0.0.1 -a -c $CONCURRENCY -i $ITERATIONS –auto-generate-sql-add-autoincrement –auto-generate-sql-secondary-indexes=$INDX –auto-generate-sql-write-number=$WRITES –auto-generate-sql-unique-write-number=$UNIQUE_WRITES –auto-generate-sql-unique-query-number=$UNIQUE_QUERIES -x $CHARS  -y $INTS –number-of-queries=$QUERIES –commit=$COMMIT –auto-generate-sql-load-type=$LOAD_TYPE "
function parse_slap {
    if [ $# -lt 1 ]; then
        AVERAGE="n/a"
        MINIMUM="n/a"
        MAXIMUM="n/a"
    else
        AVERAGE=$1
        MINIMUM=$2
        MAXIMUM=$3
    fi    
}
function run_slap {
        printf "%s\n" "SLAP $1:"
        printf "%s\n" " $CONCURRENCY simultaneous connections ||  $ITERATIONS runs through "
        printf "%s\n" " Writes : $WRITES, $UNIQUE_WRITES unique (Commit every $COMMIT) || Queries: $QUERIES, $UNIQUE_QUERIES unique "
        printf "%s\n" " Schema: $CHARS character columns, $INTS numeric with auto-increment PK and $INDX secondary indexes"
for engine in $ENGINES
    do
        SLAPPED=`$SLAP -e $engine 2>/dev/null | cut -c48-53 | tr -d \n`
"courier new,courier,monospace" size="3">        echo $SLAPPED >> $0.txt
        parse_slap $SLAPPED
            printf "For %s: %s Average, %s Min, %s Max\n" $engine $AVERAGE $MINIMUM $MAXIMUM
    done
}
run_slap "Base values"
echo
let WRITES=WRITES*10
let QUERIES=QUERIES*100
let UNIQUE_QUERIES=QUERIES/4
run_slap "more reads"
echo
UNIQUE_QUERIES=$QUERIES
UNIQUE_WRITES=$WRITES
let COMMIT=COMMIT*3
run_slap "More unique reads and writes"
echo
let INTS=INTS*5
let CHARS=CHARS*5
let INDX=INTS+CHARS-1
run_slap "wide indexed tables"
echo
let CONCURRENCY=CONCURRENCY*10
run_slap "massive concurrency"