The shell script to convert the result of sysbench to tsv format

sysbench_tsv.sh

#!/bin/sh

set -eu

readonly DB_HOST="your-cluster.local"
readonly DB_SHORTHOST=$(echo ${DB_HOST} | sed -e "s/\..*//")
readonly DB_USER="sbtest"
readonly DB_PASS="sbtest"
readonly THREADS=$1
readonly SYSBENCH_TEST="--test=oltp --oltp-test-mode=complex "
readonly LOOP=10

function print_log(){
	datetime=`date +'%Y/%m/%d %H:%M:%S'`
	echo "[${datetime}] $1"
}

function init_tsv(){
	tsvfile=$1
	echo -e "threads\ttransactions\ttps\tdeadlock\tdeadlocks/sec\tqueries\tqps\tmin(ms)\tavg(ms)\tmax(ms)\t95percentile" > ${tsvfile}
}

function exec_sysbench(){
	logfile=$1
	count=$(echo ${logfile} | sed -e 's/.*_\([0-9]*\)\.log/\1/')

	print_log "Executing **${count} time**"
	sysbench \
		${SYSBENCH_TEST} \
		--db-driver=mysql \
		--mysql-host=${DB_HOST} \
		--mysql-user=${DB_USER} \
		--mysql-password=${DB_USER} \
		--num-threads=${THREADS} run > ${logfile}
	print_log "Finished **${count} time**"
}

function convert_tsv(){
	logfile=$1
	tsvfile=$2

	print_log "Converting logfile to tsv (source: ${logfile})**"
	cat ${logfile} | egrep \
		" cat|threads:|transactions:|deadlocks|read/write|min:|avg:|max:|percentile:" \
		| tr -d "\n" \
		| sed 's/Number of threads: /\n/g' \
		| sed 's/\[/\n/g' \
		| sed 's/[A-Za-z\/]\{1,\}://g'\
		| sed 's/ \.//g' \
		| sed -e 's/read\/write//g' -e 's/approx\.  95//g' -e 's/per sec.)//g' -e 's/ms//g' -e 's/(//g' -e 's/^.*cat //g' \
		| sed 's/ \{1,\}/\t/g' >> ${tsvfile}
	print_log "Generated logfile to tsv (tsvfile: ${tsvfile})**"
}

# main

tsvfile="./sysbench_${DB_SHORTHOST}_thread${THREADS}.tsv"
init_tsv $tsvfile

for i in `seq 1 ${LOOP}`
do
	logfile="./sysbench_${DB_SHORTHOST}_thread${THREADS}_${i}.log"

	exec_sysbench ${logfile}
	convert_tsv   ${logfile} ${tsvfile}
done
print_log "Finished $0"