-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathpgbenchall
executable file
·322 lines (278 loc) · 12.4 KB
/
pgbenchall
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
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
#!/bin/bash
set -e
here="$(dirname "${BASH_SOURCE[0]}")"
start="$(date +%s)"
shared_buffers=260000 # 2031MB (leave room for additional shared memory)
#shared_buffers=262143 # 2 GB (INT_MAX / block size)
#shared_buffers=1048576 # 8 GB
global_pgoptions="-c shared_buffers=$shared_buffers"
locale=uk_UA.UTF-8
encoding=unicode
wordlist=/usr/share/dict/ukrainian
scale=300
duration=900
pgbenchoptions="-c 32 -j 4"
pgbench=/usr/local/pgsql/bin/pgbench
export LC_ALL=$locale
export LC_COLLATE=$LC_ALL
export LC_CTYPE=$LC_ALL
export LC_NUMERIC=C # c.f. REL7_3~975
export LC_TIME=C
export LC_MESSAGES=C
RELS="$(for v in /usr/local/oldpgsql/*/bin/initdb ; do echo "$v" | sed 's,.*/[0-9]*-[0-9]*-[0-9]*-\([^/]*\)/bin/initdb,\1,' ; done | sort -R)"
NRELS="$(for v in $RELS; do echo "$v" ; done | wc -l)"
N=0
if [[ -e /tmp/.s.PGSQL.5432 ]] ; then
echo /tmp/.s.PGSQL.5432 already exists
exit 1
fi
for ver in $RELS ; do
d="$(echo /usr/local/oldpgsql/*-$ver)"
rel="$(basename $d)"
now="$(date +%s)"
if [[ $N > 0 ]]; then
average=$(( ( $now - $start ) / $N ))
remaining=$(( ( $NRELS - $N ) * $average ))
eta=$(( $now + $remaining ))
remaining_str="$(( $remaining / 24 / 60 / 60 ))d $( TZ=GMT LC_ALL=C date +'%-Hh %-Mm' -d @$remaining )"
eta_str="$( LC_ALL=C date -d @$eta )"
fi
N=$(( N + 1 ))
if [[ -e /tmp/pgbenchall-quit-now ]] ; then
echo Exiting due to /tmp/pgbenchall-quit-now
exit 1
fi
if [[ ! -f $d/bin/initdb ]] ; then
echo
echo "========================================================================"
echo "Skipping $rel ($N/$NRELS)"
echo "========================================================================"
continue
fi
TESTDIR="/var/tmp/oldpgsql"
if [[ ! -d $TESTDIR ]] ; then
mkdir $TESTDIR
fi
bin="$d/bin"
lib="$d/lib"
binfallback="$(echo "$d/../*-REL7_3_21/bin")"
db="$TESTDIR/d-$rel"
log="$TESTDIR/l-$rel.log"
out="$TESTDIR/o-$rel.out"
echo
echo "========================================================================"
echo "Testing $rel ($N/$NRELS)"
echo "Current Time: $(LC_ALL=C date)"
echo "Logs in: $log"
echo "Output in: $out"
echo "Remaining: $remaining_str (average=$average remaining=$remaining)"
echo "ETA: $eta_str"
echo "========================================================================"
echo "========================================================================" >> $out
echo "Testing $rel" >> $out
echo "Start Time: $(LC_ALL=C TZ=GMT date --rfc-3339=seconds)" >> $out
if [ ! -d $db ] ; then
echo "Running initdb for $rel in $db"
initdbargs=""
if PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/initdb --help | grep -e --encoding >/dev/null ; then
initdbargs="$initdbargs --encoding $encoding"
fi
if PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/initdb --help | grep -e --locale >/dev/null ; then
initdbargs="$initdbargs --locale $LC_ALL"
fi
if PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/initdb --help | grep -e --lc-numeric >/dev/null ; then
initdbargs="$initdbargs --lc-numeric C"
fi
if PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/initdb --help | grep -e --lc-time >/dev/null ; then
initdbargs="$initdbargs --lc-time C"
fi
if PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/initdb --help | grep -e --lc-messages >/dev/null ; then
initdbargs="$initdbargs --lc-messages C"
fi
PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/initdb -D $db $initdbargs || continue
fi
if [[ -e /tmp/.s.PGSQL.5432 ]] ; then
stalepid=$(head -1 /tmp/.s.PGSQL.5432.lock)
staledir=$(tail -1 /tmp/.s.PGSQL.5432.lock)
echo stalepid=$stalepid
echo staledir=$staledir
if kill -0 $stalepid; then
echo Process $stalepid for $staledir still running
exit 1
fi
if [[ -d /proc/$stalepid ]] ; then
echo Process $stalepid for $staledir still running
exit 1
fi
echo "Cleaning up stale /tmp/.s.PGSQL.5432"
rm -f /tmp/.s.PGSQL.5432
echo "Cleaning up stale /tmp/.s.PGSQL.5432.lock"
rm -f /tmp/.s.PGSQL.5432.lock
if [[ -r $staledir/postmaster.pid ]] ; then
cat $staledir/postmaster.pid
checkpid=$(head -1 $staledir/postmaster.pid)
echo checkpid=$checkpid
if [[ $checkpid != $stalepid ]] ; then
echo "Found mismatching pid in $staledir ($checkpid != $stalepid)"
exit 1
fi
read shmkey shmid <<<$(tail -1 $staledir/postmaster.pid)
if ipcs -m --id $shmid | grep nattch=0 > /dev/null ; then
echo "Cleaning up stale shared memory segment key=$shmkey id=$shmid"
ipcs -m $shmid
ipcrm -m $shmid
else
echo "Found shared memory still attached key=$shmkey id=$shmid"
ipcs -m $shmid
exit 1
fi
echo "Cleaning up stale /$staledir/postmaster.pid"
rm -f $staledir/postmaster.pid
fi
fi
if [[ -e /tmp/.s.PGSQL.5432 ]] ; then
echo "Found stale /tmp/.s.PGSQL.5432 after cleaning up"
exit 1
fi
if [[ -e /tmp/.s.PGSQL.5432.lock ]] ; then
echo "Found stale /tmp/.s.PGSQL.5432.lock after cleaning up"
exit 1
fi
if ipcs -a | grep '[0-9]' ; then
echo "Found stale shared memory or semaphores after cleaning up"
ipcs -a
exit 1
fi
if grep checkpoint_segments $db/postgresql.conf >/dev/null; then
pgoptions="$global_pgoptions -c checkpoint_segments=32"
else
pgoptions="$global_pgoptions"
fi
trap "$bin/pg_ctl -D $db stop" EXIT
$bin/pg_ctl -D $db -l $log -o "$pgoptions" start || PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/postmaster -D $db $pgoptions > $log 2>&1 &
echo -n "Waiting for $rel to start up..."
for i in `seq 1 120` ; do
sleep 1
PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/psql -o /dev/null -X template1 -c 'select 1' 2>/dev/null && break
echo -n .
done
if PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/psql -o /dev/null -X template1 -c 'select 1' ; then
echo done
else
trap - EXIT
$bin/pg_ctl -D $db stop -m fast || echo pg_ctl exited with status $?
echo "Failed to start $rel... skipping"
continue
fi
if ! PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/psql -o /dev/null -X -c 'select 1'; then
createdbargs=""
if PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/createdb --help | grep -e '--encoding' >/dev/null; then
createdbargs="$createdbargs --encoding unicode"
fi
if PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/createdb --help | grep -e '--locale' >/dev/null; then
createdbargs="$createdbargs --locale $locale"
fi
if PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/createdb --help | grep -e '--lc_collate' >/dev/null; then
createdbargs="$createdbargs --lc_collate $locale"
fi
if PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/createdb --help | grep -e '--lc_ctype' >/dev/null; then
createdbargs="$createdbargs --lc_ctype $locale"
fi
echo "Creating and initializing db for $rel"
PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/createdb $createdbargs
fi
shared_buffers_setting="$(PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/psql -t -X -c "select setting from pg_settings where name = 'shared_buffers'" 2>/dev/null || echo $shared_buffers)"
echo "Shared Buffers: $shared_buffers_setting" >> $out
echo -n "Verifying pgbench database for $scale on $rel..."
branches=$(PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/psql -t -X -c 'select count(*) from pgbench_branches' 2>/dev/null || echo 0)
tellers=$(PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/psql -t -X -c 'select count(*) from pgbench_tellers' 2>/dev/null || echo 0)
accounts=$(PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/psql -t -X -c 'select count(*) from pgbench_accounts' 2>/dev/null || echo 0)
npkey=$(PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/psql -t -X -c "select count(*) from pg_class where relname like 'pgbench%pkey'" 2>/dev/null || echo 0)
nrels=$(PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/psql -t -X -c "select count(*) from pg_class where relname like 'pgbench%'" 2>/dev/null || echo 0)
echo done
if [[ $branches -ne $scale || $accounts -ne $(( $scale * 100000 )) || $tellers -ne $(( $scale * 10 )) || $npkey -ne 3 || $nrels -ne 7 ]] ; then
echo -n "Initializing pgbench scale factor $scale on $rel..."
echo "========================================================================" >> $out
echo "Initializing $rel (scale $scale)" >> $out
echo "Start Time: $(LC_ALL=C TZ=GMT date --rfc-3339=seconds)" >> $out
TIMEFORMAT=%0lR
time ( ( $pgbench -X -i -s $scale ) >>$out 2>&1 || echo pgbench exited with status $?)
unset TIMEFORMAT
echo "End Time: $(LC_ALL=C TZ=GMT date --rfc-3339=seconds)" >> $out
echo "========================================================================" >> $out
fi
echo -n "Verifying pgbench database for $scale on $rel..."
branches=$(PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/psql -t -X -c 'select count(*) from pgbench_branches' 2>/dev/null || echo 0)
tellers=$(PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/psql -t -X -c 'select count(*) from pgbench_tellers' 2>/dev/null || echo 0)
accounts=$(PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/psql -t -X -c 'select count(*) from pgbench_accounts' 2>/dev/null || echo 0)
npkey=$(PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/psql -t -X -c "select count(*) from pg_class where relname like 'pgbench%pkey'" 2>/dev/null || echo 0)
nrels=$(PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/psql -t -X -c "select count(*) from pg_class where relname like 'pgbench%'" 2>/dev/null || echo 0)
echo done
if [[ $branches -ne $scale || $accounts -ne $(( $scale * 100000 )) || $tellers -ne $(( $scale * 10 )) || $npkey -ne 3 || $nrels -ne 7 ]] ; then
echo "Found discrepancy in initialized database"
echo "branches=$branches accounts=$accounts tellers=$tellers npkey=$npkey nrels=$nrels"
echo "Skipping $rel due to failure to initialize pgbench"
else
# Measure file sizes for bloat stats
PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/psql -t -X -c "select relname,d||'/'||relfilenode::text from (select oid::text as d from pg_database where datname = current_user) as x, (select relfilenode,relname from pg_class where relname like '%pgbench%') as y" \
| while read tab x file; do
if [[ -n "$tab" ]] ; then
size=$(du -kc $db/base/${file}* | awk 'END {print $1}')
echo "size before pgbench of $tab: $size" >> $out
sum=$(( $sum + $size ))
else
echo "total size before pgbench: $sum" >> $out
fi
done
sync
sleep 5
sync
sleep 5
PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/psql -t -X -c 'checkpoint' 2>/dev/null
LC_ALL=C TZ=GMT $bin/pg_controldata $db > $d/before.controldata 2>/dev/null || true
# start iostat to run for $duration seconds and then print a single output
iostat -x -y $(( $duration + 30 )) 1 >> $out &
iostatpid=$!
TIMEFORMAT=%0lR
echo -n "Running Benchmark on $rel..."
time ( ( $pgbench -T $duration $pgbenchoptions -r ) >>$out 2>&1 || echo pgbench exited with status $?)
unset TIMEFORMAT
wait $iostatpid || echo iostat exited with status $?
if PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/psql -t -X -c 'checkpoint' 2>/dev/null; then
LC_ALL=C TZ=GMT $bin/pg_controldata $db > $d/after.controldata 2>/dev/null || true
else
echo "Can't run checkpoint (psql exited with status $?). Did Postgres crash?"
fi
if [[ -s $d/before.controldata && -s $d/after.controldata ]] ; then
echo "Control File Changes" >> $out
diff -u0 $d/before.controldata $d/after.controldata >> $out || true
elif [[ -e $bin/pg_controldata ]] ; then
echo "Control Files Missing!!!"
fi
# Measure file sizes for bloat stats
PATH=$bin:$PATH LD_LIBRARY_PATH=$lib $bin/psql -t -X -c "select relname,d||'/'||relfilenode::text from (select oid::text as d from pg_database where datname = current_user) as x, (select relfilenode,relname from pg_class where relname like '%pgbench%') as y" \
| while read tab x file; do
if [[ -n "$tab" ]] ; then
size=$(du -kc $db/base/${file}* | awk 'END {print $1}')
echo "size after pgbench of $tab: $size" >> $out
sum=$(( $sum + $size ))
else
echo "total size after pgbench: $sum" >> $out
fi
done
echo "End Time: $(LC_ALL=C TZ=GMT date --rfc-3339=seconds)" >> $out
echo "========================================================================" >> $out
fi
echo -n "Shutting down server..."
trap - EXIT
export PGCTLTIMEOUT=300
while [[ -e /tmp/.s.PGSQL.5432 ]] ; do
sync
sleep 1
$bin/pg_ctl -D $db stop -m fast || echo pg_ctl exited with status $? retrying
done
done
echo
echo "========================================================================"
echo " All done "
echo "========================================================================"