Last active
June 2, 2016 03:12
-
-
Save sennajox/4050006 to your computer and use it in GitHub Desktop.
A simple script for testing mysql with sysbench
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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