-
Notifications
You must be signed in to change notification settings - Fork 73
/
webreport
executable file
·222 lines (191 loc) · 10.5 KB
/
webreport
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
#!/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
#
# This currently works against the active server specified by
# in the config file. A multi-server webreport is still WIP.
# 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
mkdir -p results/$SERVERNAME # In case name is modified after run
OUTFILE="results/$SERVERNAME/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 WHERE server='${SERVERNAME}' and (not script like ':%') group by scale order by scale" > scaling.txt
gnuplot plots/scaling.plot 2>&1 | grep -v "Warning: empty"
mv -f scaling.png results/$SERVERNAME/
rm -f scaling.txt
$RESULTPSQL -At -F" " -c "select clients,round(avg(tps)) as tps from tests WHERE server='${SERVERNAME}' and (not script like ':%') group by clients order by clients" > clients.txt
gnuplot plots/clients.plot 2>&1 | grep -v "Warning: empty"
mv -f clients.png results/$SERVERNAME/
rm -f clients.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 "<body style="font-family:'Arial'">" >>$OUTFILE
echo "<h3>Averages across all test sets:</h3>" >> $OUTFILE
echo "<img src=\"scaling.png\"><p>" >> $OUTFILE
echo "<img src=\"clients.png\"><p>" >> $OUTFILE
# TODO Consider using tablefunc extension to create a dynamic crosstab instead of psql.
echo Scale vs. client TPS grid: >> $OUTFILE
$RESULTPSQL >> $OUTFILE -H -d results <<EOF
\pset footer off
SELECT scale,clients,ROUND(AVG(tps)) AS tps FROM tests WHERE not script like ':%' GROUP BY scale,clients ORDER BY scale,clients
\crosstabview
EOF
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 testset WHERE server='${SERVERNAME}' 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 server='${SERVERNAME}' AND 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 server='${SERVERNAME}' AND 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 server='${SERVERNAME}' AND set='$SET' AND (not script like ':%') group by scale order by scale" > scaling.txt
gnuplot plots/scaling.plot 2>&1 | grep -v "Warning: empty"
mv -f scaling.png results/${SERVERNAME}/scaling-$SET.png
mv -f scaling.txt scaling-$SET.txt
$RESULTPSQL -At -F" " -c "select clients,round(avg(tps)) as tps FROM tests WHERE server='${SERVERNAME}' AND set='$SET' AND (not script like ':%') group by clients order by clients" > clients.txt
gnuplot plots/clients.plot 2>&1 | grep -v "Warning: empty"
mv -f clients.png results/${SERVERNAME}/clients-$SET.png
mv -f clients.txt clients-$SET.txt
fi
echo "<img src=\"scaling-$SET.png\"><p>" >> $OUTFILE
echo "<img src=\"clients-$SET.png\"><p>" >> $OUTFILE
echo Scale vs. client TPS grid: >> $OUTFILE
$RESULTPSQL >> $OUTFILE -H -d results <<EOF
\pset footer off
SELECT scale,clients,ROUND(AVG(tps)) AS tps FROM tests WHERE server='${SERVERNAME}' AND set='${SET}' AND (not script like ':%') GROUP BY scale,clients ORDER BY scale,clients
\crosstabview
EOF
echo "<br>" >> $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 server='${SERVERNAME}' AND tests.set='$SET' AND (not script like ':%') 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 server='${SERVERNAME}' AND tests.set='$SET' AND (not script like ':%') 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 server='${SERVERNAME}' AND tests.set='$SET' AND (not script like ':%') 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,
tests.server,script,scale,clients,rate_limit,client_limit,multi,
round(tps) as tps,
CASE WHEN clients>0 THEN round(tps / clients) ELSE 0 END as per_client,
max_latency,
pg_size_pretty(round(blks_hit * 8192 / extract(epoch from (tests.end_time - tests.start_time)))::bigint) as hit_per_sec,
pg_size_pretty(round(blks_read * 8192 / extract(epoch from (tests.end_time - tests.start_time)))::bigint) as read_per_sec,
pg_size_pretty(round(buffers_alloc * 8192 / extract(epoch from (tests.end_time - tests.start_time)))::bigint) as alloc_per_sec,
pg_size_pretty(round(buffers_checkpoint * 8192 / extract(epoch from (tests.end_time - tests.start_time)))::bigint) as check_per_sec,
pg_size_pretty(round(buffers_clean * 8192 / extract(epoch from (tests.end_time - tests.start_time)))::bigint) as clean_per_sec,
pg_size_pretty(round(buffers_backend * 8192 / extract(epoch from (tests.end_time - tests.start_time)))::bigint) as backend_per_sec,
checkpoints_timed+checkpoints_req as chkpts,
maxwritten_clean as max_clean,
pg_size_pretty(max_dirty) as max_dirty,
pg_size_pretty(round(wal_written / extract(epoch from (tests.end_time - tests.start_time)))::bigint) as wal_written_per_sec
FROM test_bgwriter
RIGHT JOIN tests ON (tests.server=test_bgwriter.server AND tests.test=test_bgwriter.test)
RIGHT JOIN test_stat_database ON (tests.server=test_stat_database.server AND tests.test=test_stat_database.test)
WHERE tests.server='${SERVERNAME}' AND tests.set='$SET'
ORDER BY set,script,scale,clients,rate_limit,tests.test;" > temp.txt
# Now we need to fix lines like this
# <td align="left"><a href="results/201/">201</a></td>
# where PSQL has quoted things we wanted literally
sed-i "s/</</g" temp.txt
sed-i "s/>/>/g" temp.txt
sed-i "s/"/\"/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
# Allow some platform specific customization here. Verdana looks and
# substitutes well on Linux, while on the Mac Monaco works when small.
# (Greg personally prefers Droid Sans Mono Dotted everywhere)
PNGTERM="set terminal pngcairo size 640,480 enhanced font 'Verdana,10'"
if [[ `uname` = "Darwin" ]] ; then
PNGTERM="set terminal pngcairo size 640,480 enhanced font 'Monaco,10'"
fi
# Plot set comparison
echo "${PNGTERM}" > multi-client.plot
cat >> multi-client.plot << "ENDING"
set output "clients-sets.png"
set title "pgbench transactions/sec"
set grid xtics ytics
set key bottom right
set xlabel "Clients"
set ylabel "TPS"
plot \
ENDING
echo "${PNGTERM}" > multi-scale.plot
cat >> multi-scale.plot << "ENDING"
set output "scaling-sets.png"
set title "pgbench transactions/sec"
set grid xtics ytics
set key top right
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 server='${SERVERNAME}' AND set='${SET}'"`
if [ "$SET" -eq "$LASTSET" ] ; then
DELIM=""
else
DELIM=",\\"
fi
echo "'scaling-$SET.txt' using 1:3 axis x1y1 title '$DESCR' with linespoints linewidth 2 pointtype 7 pointsize 1.5 $DELIM" >> multi-scale.plot
echo "'clients-$SET.txt' using 1:2 axis x1y1 title '$DESCR' with linespoints linewidth 2 pointtype 7 pointsize 1.5$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 -f clients-sets.png results/$SERVERNAME/
mv -f scaling-sets.png results/$SERVERNAME/
rm -f multi-scale.plot
rm -f multi-client.plot
fi
for SET in $SETS ; do
rm -f scaling-$SET.txt clients-$SET.txt
done
rm -f temp.txt
cat templates/footer.html >> $OUTFILE