run-pgbench.sh

application/x-shellscript

Download

#!/usr/bin/env bash

set -e

PATH_OLD=$PATH
DATADIR=/mnt/data/pg
DBNAME=test

export PATH=/home/azureuser/builds/master/bin:$PATH_OLD

# kill and remove running instance (if any)
killall -9 postgres || true
sleep 5
rm -Rf $DATADIR

TS=$(date +%Y%m%d-%H%M%S)

mkdir $TS

pg_ctl -D $DATADIR init

pg_checksums --disable $DATADIR

pg_ctl -D $DATADIR -l pg.log start

psql postgres -c "alter system set max_wal_size = '64GB'"
psql postgres -c "alter system set shared_buffers = '32GB'"
psql postgres -c "alter system set io_method = 'worker'"
psql postgres -c "alter system set io_workers = 12"

pg_ctl -D $DATADIR -l pg.log restart

for sb in 8 32; do

	dropdb --if-exists $DBNAME

	createdb $DBNAME

	for pct in 50 90 110; do

		mkdir -p $TS/$sb/$pct

		scale=$((sb*1024*100/15/pct))

		pgbench -i -s $scale $DBNAME > $TS/$sb/$pct/init.log 2>&1

		psql postgres -c "alter system set shared_buffers = '${sb}MB'"


		for p in none colocated random; do

			for b in master patched-0001 patched-0002 patched-0003; do

				mkdir -p $TS/$sb/$pct/$p/$b

				export PATH=/home/azureuser/builds/$b/bin:$PATH_OLD

				pg_ctl -D $DATADIR -l $TS/$sb/$pct/$p/$b/pg.log stop
				pg_ctl -D $DATADIR -l $TS/$sb/$pct/$p/$b/pg.log start

				# short warmup for this build
				pgbench -S -M prepared -c 64 -j 64 -P 1 -T 60 $DBNAME > $TS/$sb/$pct/$p/$b/warmup.log 2>&1

				for m in simple prepared; do

					for r in $(seq 1 5); do

						mkdir -p $TS/$sb/$pct/$p/$b/$m/$r

						for c in 1 16 64; do

							if [ "$p" == "none" ]; then
								pgbench -n -S -M $m -c $c -j $c -P 1 -T 60 $DBNAME > $TS/$sb/$pct/$p/$b/$m/$r/$c.log 2>&1
							else
								pgbench -n -S -M $m -c $c -j $c -P 1 -T 60 --pin-cpus $p $DBNAME > $TS/$sb/$pct/$p/$b/$m/$r/$c.log 2>&1
							fi

							tps=$(grep '^tps = ' $TS/$sb/$pct/$p/$b/$m/$r/$c.log | awk '{print $3}')

							echo $sb $pct $scale $p $b $m $r $c $tps >> $TS/results.csv 2>&1

						done

					done

				done

			done

		done

	done

done

pg_ctl -D $DATADIR -l pg.log stop