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 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 "Syncs\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" or cmd = "user-transmit") and completedLapse > 1
GROUP by cmd;

.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" or cmd = "cmd-transmit" and completedlapse > 1;

.print "\n"
select CAST ((julianday(max(endtime)) - julianday(min(starttime))) * 24 * 60 * 60 as INTEGER) as SecondsDuration 
from process where cmd = 'user-sync' or cmd = 'user-transmit';

.print "\n"

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

grep parallel config.out

echo ""
echo "Workspace sizes (for cross check)"
echo "Client: Files Size"
cat client_sizes.txt | awk '{f += $2; s += $4+0} END { printf "%d %.2fG\n", f, s;}'

function getstats() {
    nic=$1
    rx=$(grep -A7 "$nic:" network.out | grep "RX packets" | awk 'FNR==1{s = $5} FNR==2{e = $5} END {gb=1024*1024*1024; printf "%.2f", (e - s) / gb}')
    tx=$(grep -A7 "$nic:" network.out | grep "TX packets" | awk 'FNR==1{s = $5} FNR==2{e = $5} END {gb=1024*1024*1024; printf "%.2f", (e - s) / gb}')
    echo "$nic: RX ${rx}G TX ${tx}G"
}

echo ""
nics="bond0 enp66s0 enp66s0d1"
for nic in $nics
do
    getstats $nic
done

echo ""