A simple script for testing mysql with sysbench

run_sysbench.sh

#!/bin/bash

if [ $# -lt 2 ]; then
    echo "usage:$0 test_name {prepare|run|cleanup|gen}"
    exit 1
fi

test_name="$1"
cmd="$2"

test="oltp"
driver="mysql"
host="127.0.0.1"
usr="root"
pwd=""
port=3306
engine="innodb"
max_time="120"
max_req="100000"
# the sizes
table_sizes=(1000000 3000000 5000000)
# concurrency
#threads=(10 50 100 150)
threads=(10 50 60 100 150)

common="--test=$test --db-driver=$driver --mysql-host=$host --mysql-port=$port --mysql-user=$usr  --mysql-table-engine=$engine"
if [ -n "$pwd" ]; then
    common="$common --mysql-password=$pwd"
fi

function do_prepare()
{
    for table_size in "${table_sizes[@]}"
    do
        db="set$table_size"
        if [ -z "$pwd" ];then
            mysql -h$host -P$port -u$usr -e "create database if not exists $db"
        else
            mysql -h$host -P$port -u$usr -p$pwd -e "create database if not exists $db"
        fi
        sysbench $common --oltp-table-size=$table_size --mysql-db=$db prepare
    done
}

function do_test()
{
    mkdir -p $test_name
    cd $test_name
    for table_size in "${table_sizes[@]}"
    do
        db="set$table_size"
        for thread_num in "${threads[@]}"
        do
            sysbench $common --oltp-table-size=$table_size --mysql-db=$db  --max-time=$max_time --max-requests=$max_req --num-threads=$thread_num run > $test_name"_"$db"_"$thread_num".log"
        done
    done
    cd -
}

function gen_table()
{
    cd $test_name
    for table_size in "${table_sizes[@]}"
    do
        db="set$table_size"
        prefix="$test_name""_""$db"
        result="$prefix.result"
	echo > $result
        echo "$test_name TPS QPS" >> $result
        for thread_num in "${threads[@]}"
        do
            tps=`cat $test_name"_"$db"_"$thread_num".log" | grep transactions: | awk -F"(" '{print $2}' | awk -F"per" '{print $1}'`
            qps=`cat $test_name"_"$db"_"$thread_num".log" | grep read/write | awk -F"(" '{print $2}' | awk -F"per" '{print $1}'`
            echo "$prefix""_threads_$thread_num $tps $qps" >> $result
        done
    done
    cd -
}

function do_cleanup()
{
    for table_size in "${table_sizes[@]}"
    do
        db="set$table_size"
        sysbench $common --mysql-db=$db cleanup
    done
}


case "$cmd" in
    prepare)
        do_prepare
        ;;

    run)
        do_test
        ;;

    cleanup)
        do_cleanup
	;;

    gen)
        gen_table
	;;

    *)
        echo "usage:$0 [prepare|run|cleanup]"
        exit 1
esac