webreport

#!/bin/bash

#
# webreport.sh connects to the Results database and generates a web page
# as index.htm that includes links to all the results in the database
#

# TODO gnuplot badly handles default font selection on many platforms.
# Despite specifying "medium" as the font, it still looks for Arial
# which often is only there if you've installed Microsoft Core Fonts.
# That results in this (harmless) warning constantly appearing:
#   Could not find/open font when opening font "arial", using internal non-scalable font
# On RHEL6 it's possible to switch to fonts other than Arial like this:
#   export GDFONTPATH=/usr/share/fonts/liberation
#   export GNUPLOT_DEFAULT_GDFONT="LiberationMono-Regular"
# It's not clear if it's worth detecting that font is available and
# switching to it though.  Tests so far suggest it's slightly worse
# than the internal font, and LiberationSans looks terrible.

source ./config
OUTFILE="results/index.htm"
RESULTPSQL="psql -h $RESULTHOST -U $RESULTUSER -p $RESULTPORT -d $RESULTDB"

# Emulate 'sed -i' behavior from GNU sed with standard sed instead.
# Needed on platforms like Solaris.
function sed-i {
  replace=$1
  filename=$2
  sed "$replace" ${filename} > ${filename}.new
  mv ${filename}.new ${filename}
}

if [ -z $GNUPLOT ] || [ ! -x $GNUPLOT ]; then
  echo "gnuplot not found, skipping webreport charts generation"
else
  # Produce combined report, averaged across all test sets
  $RESULTPSQL -At -F" " -c "select scale,round(avg(dbsize) / (1024 * 1024)) as dbsize,round(avg(tps)) as tps from tests group by scale order by scale" > scaling.txt
  gnuplot plots/scaling.plot 2>&1 | grep -v "Warning: empty"
  mv scaling.png results/
  rm scaling.txt

  $RESULTPSQL -At -F" " -c "select clients,round(avg(tps)) as tps from tests group by clients order by clients" > clients.txt
  gnuplot plots/clients.plot 2>&1 | grep -v "Warning: empty"
  mv clients.png results/
  rm clients.txt

  $RESULTPSQL -At -F" " -c "select scale,clients,round(avg(tps)) as tps from tests group by scale,clients order by scale,clients" > 3d.txt
  gnuplot plots/3d.plot 2>&1 | grep -v "Warning: empty"
  mv 3d.png results/
  rm 3d.txt
fi

# Copy the header HTML template to our outfile
if [ "$TABBED" -eq "1" ]; then
  cp templates/header-tabbed.html $OUTFILE
else
  cp templates/header.html $OUTFILE
fi

echo "<h3>Averages across all test sets:</h3>" >> $OUTFILE
echo "<img src=\"scaling.png\"><p>" >> $OUTFILE
echo "<img src=\"clients.png\"><p>" >> $OUTFILE
echo "<img src=\"3d.png\"><p>" >> $OUTFILE
echo "<h3>Test sets comparison:</h3>" >> $OUTFILE
echo "<img src=\"scaling-sets.png\"><p>" >> $OUTFILE
echo "<img src=\"clients-sets.png\"><p>" >> $OUTFILE

# Loop over all the active test sets
SETS=`$RESULTPSQL -A -t -c "select set from tests group by set order by set"`

# Build table of contents
echo '<ul>' >> $OUTFILE
for SET in $SETS ; do
  DESCR=`$RESULTPSQL -A -t -c "select info from testset where set='$SET'"`
  echo "<li><a href='#set-$SET'>Test Set $SET - $DESCR</a></li>" >> $OUTFILE
done
echo '</ul>' >> $OUTFILE

for SET in $SETS ; do
  DESCR=`$RESULTPSQL -A -t -c "select info from testset where set='$SET'"`
  echo "<div id='set-$SET'>" >> $OUTFILE
  echo "<hr><h3>Set" $SET : $DESCR"</h3>" >> $OUTFILE

  # We'll need to know the last set plot for the multi-plot below
  LASTSET="$SET"

  if [ ! -z $GNUPLOT ] && [ -x $GNUPLOT ]; then
    # Generate graphs for just this test set
    $RESULTPSQL -At -F" " -c "select scale,round(avg(dbsize) / (1024 * 1024)) as dbsize,round(avg(tps)) as tps from tests where set='$SET' group by scale order by scale" > scaling.txt
    gnuplot plots/scaling.plot 2>&1 | grep -v "Warning: empty"
    mv scaling.png results/scaling-$SET.png
    mv scaling.txt scaling-$SET.txt

    $RESULTPSQL -At -F" " -c "select clients,round(avg(tps)) as tps from tests where set='$SET' group by clients order by clients" > clients.txt
    gnuplot plots/clients.plot 2>&1 | grep -v "Warning: empty"
    mv clients.png results/clients-$SET.png
    mv clients.txt clients-$SET.txt

    $RESULTPSQL -At -F" " -c "select scale,clients,round(avg(tps)) as tps from tests where set='$SET' group by scale,clients order by scale,clients" > 3d.txt
    gnuplot plots/3d.plot 2>&1 | grep -v "Warning: empty"
    mv 3d.png results/3d-$SET.png
    rm 3d.txt
  fi

  echo "<img src=\"scaling-$SET.png\"><p>" >> $OUTFILE
  echo "<img src=\"clients-$SET.png\"><p>" >> $OUTFILE
  # The 3D results set isn't that useful, don't want to repeat it for
  # every single set, too
  # echo "<img src=\"3d-$SET.png\"><p>" >> $OUTFILE

  # Summarize the test set
  echo Averages for test set $SET by scale: >> $OUTFILE
  $RESULTPSQL -H -c "select set,scale,round(avg(tps)) as tps,round(1000*avg(avg_latency))/1000 as avg_latency,round(1000*avg(percentile_90_latency))/1000 as \"90%<\",round(1000 * avg(max_latency))/1000 as max_latency from tests where tests.set='$SET' group by set,scale order by set,scale;" >> $OUTFILE

  echo Averages for test set $SET by clients: >> $OUTFILE
  $RESULTPSQL -H -c "select set,clients,round(avg(tps)) as tps,round(1000*avg(avg_latency))/1000 as avg_latency,round(1000*avg(percentile_90_latency))/1000 as \"90%<\",round(1000 * avg(max_latency))/1000 as max_latency from tests where tests.set='$SET' group by set,clients order by set,clients;" >> $OUTFILE

  echo Averages for test set $SET by scale, client, and rate limit: >> $OUTFILE
  $RESULTPSQL -H -c "select set,scale,clients,rate_limit,round(avg(tps)) as tps,round(1000*avg(avg_latency))/1000 as avg_latency,round(1000*avg(percentile_90_latency))/1000 as \"90%<\",round(1000 * avg(max_latency))/1000 as max_latency from tests where tests.set='$SET' group by set,scale,clients,rate_limit order by set,scale,clients,rate_limit;" >> $OUTFILE

  echo Detail for test set $SET: >> $OUTFILE
  # Create a line showing the results for every test as an HTML table  
  $RESULTPSQL -H -c "select set,'<a href=\"' || tests.test || '/index.html\">' || tests.test || '</a>' as test,scale,clients,rate_limit,round(tps) as tps,max_latency, checkpoints_timed+checkpoints_req as chkpts,buffers_checkpoint as buf_check,buffers_clean as buf_clean,buffers_backend as buf_backend,buffers_alloc as buf_alloc, maxwritten_clean as max_clean, buffers_backend_fsync as backend_sync, max_dirty, wal_written,cleanup from test_bgwriter right join tests on tests.test=test_bgwriter.test where tests.set='$SET' order by set,scale,clients,rate_limit,tests.test;" > temp.txt

  # Now we need to fix lines like this
  # <td align="left">&lt;a href=&quot;results/201/&quot;&gt;201&lt;/a&gt;</td>
  # where PSQL has quoted things we wanted literally
  sed-i "s/&lt;/</g" temp.txt
  sed-i "s/&gt;/>/g" temp.txt
  sed-i "s/&quot;/\"/g" temp.txt
  cat temp.txt >> $OUTFILE
  # Remove row counts
  cp $OUTFILE temp.txt
  cat temp.txt | grep -v " rows)" > $OUTFILE
  echo "</div>" >> $OUTFILE
done

# Plot set comparison
cat > multi-client.plot << "ENDING"
set terminal png medium size 640,480
set output "clients-sets.png"
set title "pgbench transactions/sec"
set grid xtics ytics
set xlabel "Clients"
set ylabel "TPS"
plot \
ENDING

cat > multi-scale.plot << "ENDING"
set terminal png medium size 640,480
set output "scaling-sets.png"
set title "pgbench transactions/sec"
set grid xtics ytics
set xlabel "Scaling factor"
set ylabel "TPS"
plot \
ENDING

for SET in $SETS ; do
  # Trimmed down descriptions needed to fit into the graph key
  DESCR=`$RESULTPSQL -A -t -c "select substring(info from 1 for 35) from testset where set='$SET'"`

  if [ "$SET" -eq "$LASTSET" ] ; then
    DELIM=""
  else
    DELIM=",\\"
  fi

  echo  "'scaling-$SET.txt' using 1:3 axis x1y1 title '$DESCR' with linespoints $DELIM" >> multi-scale.plot
  echo  "'clients-$SET.txt' using 1:2 axis x1y1 title '$DESCR' with linespoints $DELIM" >> multi-client.plot
done

if [ ! -z $GNUPLOT ] && [ -x $GNUPLOT ]; then
  gnuplot multi-scale.plot 2>&1 | grep -v "Warning: empty"
  gnuplot multi-client.plot 2>&1 | grep -v "Warning: empty"
  mv clients-sets.png results/
  mv scaling-sets.png results/
  rm multi-scale.plot
  rm multi-client.plot
fi

for SET in $SETS ; do
  rm -f scaling-$SET.txt clients-$SET.txt
done

rm temp.txt
cat templates/footer.html >> $OUTFILE