Sysbench for MySQL Testing

1. For whole testing scripts

    host=localhost
    port=3306
    socket=/home/data/mysql/mysql.sock
    user=root
    password=123456

    resultsdir=./results-thread

    threads="8 16 32 64 128"

    sizes="1000000 5000000 10000000 15000000 20000000 25000000 30000000"


    printf "sizes,threads,transactions,trns p/s,deadlocks, dls p/s,read/write requests,r/w reqs p/s,min,avg,max,99 percentile \n" >> stat.txt

    mkdir -p $resultsdir

    for thread in $threads;do
        mkdir $resultsdir/thread-$thread
        for size in $sizes; do
            sysbench --test=oltp --mysql-table-engine=innodb \
            --oltp-table-size=$size  --mysql-socket=$socket \
            --mysql-user=$user --mysql-host=$host \
            --mysql-password=$password --mysql-db=students \
            --oltp-table-name=test$size prepare;
            sysbench --test=oltp --mysql-table-engine=innodb \
            --oltp-table-size=$size --mysql-socket=$socket \
            --mysql-user=$user --mysql-host=$host \
            --mysql-password=$password --mysql-db=students \
            --oltp-table-name=test$size  --max-requests=1000 \
            --num-threads=$thread run | \ 
            tee -a $resultsdir/thread-$thread/sysbench.$thread.$size.report;
            sysbench --test=oltp --mysql-host=$host  --mysql-user=$user \
            --mysql-password=$password --mysql-socket=$socket \
            --mysql-db=students --oltp-table-name=test$size  cleanup;

            cat $resultsdir/thread-$thread/sysbench.$thread.$size.report | \
            egrep "cat|threads:|transactions:|deadlocks|
            read/write|min:|avg:|max:|percentile:" | \
            sed  -e '1 s/Number of threads: //' | \
            tr -d "\n" | \
            sed -e 's/Number of threads: /\n/g' \
            -e 's/[A-Za-z\/]\{1,\}://g' \
            -e 's/read\/write//g' \
            -e 's/approx\.  95//g' \
            -e 's/per sec.)//g' \
            -e 's/ms//g' \
            -e 's/(//g'  \
            -e 's/  */,/g' | awk -v d=$size '{$0=d","$0}1' >> stat.txt
        done
    done

2. Results

SIZES,THREADS,TRANSACTIONS,TRNS P/S,DEADLOCKS,DLS P/S,READ/WRITE REQUESTS,R/W REQS P/S,MIN,AVG,MAX,99 PERCENTILE
1000000,8,1000,236.13,0,0.00,19000,4486.45,7.16,33.66,118.17,59.32
5000000,8,1000,265.81,0,0.00,19000,5050.47,6.35,29.99,99.14,46.96
10000000,8,1000,379.46,127,48.19,21413,8125.43,3.54,21.01,306.08,34.70
15000000,8,1000,202.33,0,0.00,19000,3844.31,8.76,39.36,113.15,62.77
20000000,8,1000,185.34,0,0.00,19000,3521.38,10.48,43.05,111.56,66.92
25000000,8,1000,171.11,0,0.00,19000,3251.01,7.46,46.69,506.47,74.60
30000000,8,1000,163.24,0,0.00,19000,3101.55,14.49,48.91,504.90,77.21
1000000,16,1000,288.82,0,0.00,19000,5487.51,7.82,54.95,168.84,89.33
5000000,16,1000,270.04,0,0.00,19000,5130.82,19.13,59.02,323.48,92.27
10000000,16,1000,231.99,0,0.00,19000,4407.90,5.73,68.45,329.40,112.02
15000000,16,1000,230.78,0,0.00,19000,4384.76,8.29,69.03,233.97,124.21
20000000,16,1000,170.32,0,0.00,19000,3236.17,9.48,93.60,799.09,158.71
25000000,16,1000,164.44,0,0.00,19000,3124.34,5.68,96.44,633.64,165.06
30000000,16,1000,174.87,0,0.00,19000,3322.48,9.50,90.89,520.92,151.65
1000000,32,1000,316.29,0,0.00,19000,6009.43,7.72,99.23,346.24,172.95
5000000,32,1000,248.03,0,0.00,19000,4712.60,23.21,125.06,383.60,208.35
10000000,32,1000,229.40,0,0.00,19000,4358.52,5.39,138.82,598.64,350.74
15000000,32,1000,211.46,0,0.00,19000,4017.80,13.27,150.13,501.56,284.44
20000000,32,1000,198.36,0,0.00,19000,3768.77,8.54,159.58,822.41,252.87
25000000,32,1000,181.85,0,0.00,19000,3455.19,21.85,174.35,891.91,271.78
30000000,32,1000,156.00,0,0.00,19000,2964.03,9.45,204.21,811.06,339.07
1000000,64,1000,311.37,0,0.00,19000,5916.07,19.86,198.48,562.27,369.82
5000000,64,1000,287.12,0,0.00,19000,5455.27,29.12,216.80,843.55,338.67
10000000,64,1000,222.90,0,0.00,19000,4235.16,13.82,285.81,975.70,773.48
15000000,64,1000,193.57,0,0.00,19000,3677.74,12.19,327.30,1143.78,846.66
20000000,64,1000,170.73,0,0.00,19000,3243.88,55.67,370.57,1217.91,1092.95
25000000,64,1000,157.72,0,0.00,19000,2996.74,11.59,401.83,1680.79,1306.81
30000000,64,1000,158.19,0,0.00,19000,3005.66,19.20,401.41,1304.60,1107.11
1000000,128,1000,302.16,0,0.00,19000,5741.04,52.05,408.05,1041.78,772.32
5000000,128,1000,251.01,0,0.00,19000,4769.20,26.99,489.92,1724.37,781.39
10000000,128,1000,207.77,0,0.00,19000,3947.55,102.64,604.39,1577.85,1285.85
15000000,128,1000,149.60,0,0.00,19000,2842.35,146.68,841.09,3348.05,2764.42
20000000,128,1000,145.41,0,0.00,19000,2762.83,132.31,865.04,3247.91,2620.98
25000000,128,1000,139.95,0,0.00,19000,2658.97,252.97,896.25,2942.05,2773.54
30000000,128,1000,115.80,0,0.00,19000,2200.18,221.51,1089.31,4408.08,4141.01

3. Visulization

library("ggplot2")
df <- read.csv("C:\\Users\\tanhao\\Documents\\GitHub\\DM\\mysql\\work\\stat.csv",head=TRUE)
ggplot(df, aes(x = factor(sizes), y = trns.p.s, fill=factor(threads))) + geom_bar(stat = "identity", position="dodge")

image

以上数据为一台1核1G主机的性能测试结果.

欢迎参见Details

Comments