sqlreport.sh #5

#!/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 120

.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;

.width 12 9 5 120

.print "\n"
.print "Submits by IP per second\n"
select IP, 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 IP, time;

.print "\n"
.width 0 0 0 0

.print "Submits\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"

.print "By IP"\n
select ip, 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
group by ip;

.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