sqlreport.sh #4

#!/bin/bash

function bail () { echo "\nError: ${1:-Unknown Error}\n"; exit ${2:-1}; }

# Runs SQL report for specified run
rundir=${1:-Unset}
[[ $rundir == "Unset" ]] && bail "Specify rundir as parameter"

[[ -d "run/$rundir" ]] && rundir="run/$rundir"

cd $rundir || bail "Rundir $rundir doesn't exist"

config=config.out
instance=$(grep P4PORT $config | perl -ne 'print $1 if /([0-9])666/')

sqlreport=sql.txt


cat > sql.in <<EOF
.mode column
.output $sqlreport

select cmd, count(cmd), 
round(cast(avg(completedLapse) AS DECIMAL(9, 3)), 3) as "Avg Time", 
round(cast(max(completedLapse) AS DECIMAL(9, 3)), 3) as "Max Time",
round(cast(sum(completedLapse) AS DECIMAL(9, 3)), 3) as "Sum Time"
from process
group by cmd;

.print "\n"

.width 9 5 90

.print "Submits per second\n"
select substr(endtime, 12, 8) as time, count(cmd) as cmds,
replace(substr(quote(zeroblob(COUNT(cmd) / 2)), 3, COUNT(cmd)), '0', '*') AS bar
from process
where cmd = "dm-CommitSubmit"
group by time;

.print "\n"

select cmd, count(cmd),  round(cast(avg(completedLapse) AS DECIMAL(9,2)), 2) as "Avg Time", 
round(cast(max(completedLapse) AS DECIMAL(9, 2)), 2) as "Max Time",
cast(sum(completedLapse) as decimal(9,2)) as "Sum Time",
round(cast(avg(rpcsizeout) AS DECIMAL(9,2)), 2) as "Avg Sent(MB)",
cast(sum(rpcsizeout) AS DECIMAL(9,2)) as "Sum Sent(MB)",
round(cast(sum(rpcsizeout) as decimal(9,2)) / cast(sum(completedLapse) as decimal(9,2)), 2) as "Rate MB/s"
FROM process where (cmd = "user-sync") and completedLapse > 1;

.print "\n"

select min(substr(starttime, 12, 8)), max(substr(endtime, 12, 8)), count(completedlapse) from process where cmd = "user-sync" and completedlapse > 1;
EOF

sqlite3 -header run.db < sql.in

echo "" >> $sqlreport
echo "Report for instance: $instance" >> $sqlreport
ls -l /p4/$instance/root >> $sqlreport
echo "$rundir" >> $sqlreport
echo "" >> $sqlreport
cat $sqlreport