Script for benchmarking mysql server using sysbench

mysqlbench.sh

#!/bin/bash

# Instructions to run the script:
#
# 1. Create a database named sbtest in your mysql database.
# 2. Change following variables according to your mysqld setup
# 3. Run the script
#
# You can find your server socket location in path/to/your/mysqld/build/directory/data/error.log file.
MYSQL_SOCKET=/mnt/storage/sajib/repos/mysql_build/mysqld.sock
# Your mysqld user
MYSQL_USER="root"
# Your mysqld user password.
MYSQL_PASSWORD="MyNewPass"


TIME=180
TABLE_SIZE=100000
NUM_RUNS=3

if [ -n "$1" ]; then
    TIME="$1"
fi

if [ -n "$2" ]; then
    TABLE_SIZE="$2"
fi

if [ -n "$3" ]; then
    NUM_RUNS="$3"
fi

for i in $(seq 1 $NUM_RUNS); do

    echo -e "Executing OLTP_INSERT for ${TIME}s:\n"
    echo -e "Run: ${i}\n"
    sysbench oltp_insert --mysql-socket=$MYSQL_SOCKET --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASSWORD --mysql-db=sbtest --db-driver=mysql --tables=3 --table-size=$TABLE_SIZE --delete_inserts=10 --threads=4 --time=$TIME prepare
    #sleep 5
    OUT="$(sysbench oltp_insert --mysql-socket=$MYSQL_SOCKET --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASSWORD --mysql-db=sbtest --db-driver=mysql --tables=3 --table-size=$TABLE_SIZE --delete_inserts=10 --threads=4 --time=$TIME run)"
    sysbench oltp_insert --mysql-socket=$MYSQL_SOCKET --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASSWORD --mysql-db=sbtest --db-driver=mysql --tables=3 --table-size=$TABLE_SIZE --delete_inserts=10 --threads=4 --time=$TIME cleanup

    #sleep 5

    echo -e "\nExecuting OLTP_DELETE for ${TIME}s:\n"
    echo -e "Run: ${i}\n"
    sysbench oltp_delete --mysql-socket=$MYSQL_SOCKET --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASSWORD --mysql-db=sbtest --db-driver=mysql --tables=3 --table-size=$TABLE_SIZE --delete_inserts=10 --threads=4 --time=$TIME prepare
    #sleep 5
    OUT="${OUT}$(sysbench oltp_delete --mysql-socket=$MYSQL_SOCKET --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASSWORD --mysql-db=sbtest --db-driver=mysql --tables=3 --table-size=$TABLE_SIZE --delete_inserts=10 --threads=4 --time=$TIME run)"
    sysbench oltp_delete --mysql-socket=$MYSQL_SOCKET --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASSWORD --mysql-db=sbtest --db-driver=mysql --tables=3 --table-size=$TABLE_SIZE --delete_inserts=10 --threads=4 --time=$TIME cleanup

    #sleep 5

    echo -e "\nExecuting OLTP_POINT_SELECT for ${TIME}s:\n"
    echo -e "Run: ${i}\n"
    sysbench oltp_point_select --mysql-socket=$MYSQL_SOCKET --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASSWORD --mysql-db=sbtest --db-driver=mysql --tables=3 --table-size=$TABLE_SIZE --threads=4 --time=$TIME prepare
    #sleep 5
    OUT="${OUT}$(sysbench oltp_point_select --mysql-socket=$MYSQL_SOCKET --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASSWORD --mysql-db=sbtest --db-driver=mysql --tables=3 --table-size=$TABLE_SIZE --threads=4 --time=$TIME run)"
    sysbench oltp_point_select --mysql-socket=$MYSQL_SOCKET --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASSWORD --mysql-db=sbtest --db-driver=mysql --tables=3 --table-size=$TABLE_SIZE --threads=4 --time=$TIME cleanup

    RUN_OUT=$(echo -e "$OUT" | awk 'BEGIN {i=0;j=0} {if($1 == "transactions:") trans[i++]=substr($3,2); if($1 == "95th") lat[j++]=$3} END {for(m=0; m < i; m++) printf("%s ", trans[m]); printf("\n"); for(m=0; m < j; m++) printf("%s ", lat[m]); printf("\n")}')
    TOTAL_OUT="${TOTAL_OUT}\n${RUN_OUT}"

    echo -e "${RUN_OUT}"
    echo -e "${TOTAL_OUT}"

done

echo -e "${TOTAL_OUT}" | awk '

BEGIN {
    i=0;
    j=0;
    k=0;
}

{
    if(i % 2 != 0) {
        ins_tps[j]=$1;
        del_tps[j]=$2;
        sel_tps[j]=$3;
        j++;
    }
    else {
        ins_lat[k]=$1;
        del_lat[k]=$2;
        sel_lat[k]=$3;
        k++;
    }

    i++;
}

END {
    n=int(NR/2);

    # Avg. tps
    for(m=0; m < n; m++) {
        tps_tot[0] += ins_tps[m];
        tps_tot[1] += del_tps[m];
        tps_tot[2] += sel_tps[m];
    }

    for(i = 0; i < 3; i++) {
        tps_avg[i] = tps_tot[i] / n;
    }
    printf("\navg. tps: %lf %lf %lf\n" , tps_avg[0], tps_avg[1], tps_avg[2]);

    # standard deviations
    for(m = 0; m < n; m++) {
        dev_sqr[0] += ((ins_tps[m] - tps_avg[0]) * (ins_tps[m] - tps_avg[0])); 
        dev_sqr[1] += ((del_tps[m] - tps_avg[1]) * (del_tps[m] - tps_avg[1])); 
        dev_sqr[2] += ((sel_tps[m] - tps_avg[2]) * (sel_tps[m] - tps_avg[2])); 
    }
    
    for(i = 0; i < 3; i++) {
        tps_sd[i] = sqrt((dev_sqr[i] / (n -1)));
    }
    printf("sd. tps: %lf %lf %lf\n", tps_sd[0], tps_sd[1], tps_sd[2]);
    printf("sd. tps%: %lf %lf %lf\n", (tps_sd[0] / tps_avg[0]) * 100, (tps_sd[1] / tps_avg[1]) * 100, (tps_sd[2] / tps_avg[2]) * 100);

    # Avg. latency
    for(m=1; m <= n; m++) {
        lat_tot[0] += ins_lat[m];
        lat_tot[1] += del_lat[m];
        lat_tot[2] += sel_lat[m];
    }
    
    for(i = 0; i < 3; i++) {
        lat_avg[i] = lat_tot[i] / n;
    }
    printf("\navg. lat: %lf %lf %lf\n" , lat_avg[0], lat_avg[1], lat_avg[2]);
    
    # standard deviations
    dev_sqr[0] = 0;
    dev_sqr[1] = 0;
    dev_sqr[2] = 0;
    
    for(m = 1; m < (n + 1); m++) {
        dev_sqr[0] += ((ins_lat[m] - lat_avg[0]) * (ins_lat[m] - lat_avg[0])); 
        dev_sqr[1] += ((del_lat[m] - lat_avg[1]) * (del_lat[m] - lat_avg[1])); 
        dev_sqr[2] += ((sel_lat[m] - lat_avg[2]) * (sel_lat[m] - lat_avg[2])); 
    }
    
    for(i = 0; i < 3; i++) {
        lat_sd[i] = sqrt((dev_sqr[i] / (n -1)));
    }
    printf("sd. lat: %lf %lf %lf\n", lat_sd[0], lat_sd[1], lat_sd[2]);
    printf("sd. lat%: %lf %lf %lf\n", (lat_sd[0] / lat_avg[0]) * 100, (lat_sd[1] / lat_avg[1]) * 100, (lat_sd[2] / lat_avg[2]) * 100);
} '