Skip to content

Instantly share code, notes, and snippets.

@sennajox
Last active June 2, 2016 03:12
Show Gist options
  • Save sennajox/4050006 to your computer and use it in GitHub Desktop.
Save sennajox/4050006 to your computer and use it in GitHub Desktop.
A simple script for testing mysql with sysbench
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment