run-pgbench.sh

application/x-shellscript

Download

#!/usr/bin/env bash

set -e

PATH_OLD=$PATH
DATADIR=/mnt/raid-nvme/data-fk-batch
BRANCHES="master fk-batch-64 fk-batch-16 fk-batch-256"
BUILDS=/home/tomas/builds
BUILDSHP=/home/tomas/builds-tmpfs
RUNS=3

export MALLOC_TOP_PAD_=$((64*1024*1024))

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

killall -9 postgres || true
sleep 1

rm -Rf $DATADIR

# copy all binaries into the huge-page tmpfs mount
rm -Rf $BUILDSHP/*
cp -R $BUILDS/* $BUILDSHP/


PATH=$BUILDSHP/master/bin:$PATH_OLD

pg_ctl -D $DATADIR init
pg_checksums --disable $DATADIR

for rows_1 in 10000000; do
#for rows_1 in 100000 1000000 10000000; do

	for rows_2 in 1 2 5 10 100 1000; do

		for order_1 in seq rand; do

			for sb in 128MB 8GB; do

				#for tt in logged unlogged; do
				for tt in unlogged; do

					if [ "$tt" == "logged" ]; then
						t=""
					else
						t="unlogged"
					fi

					for b in $BRANCHES; do

						cp postgresql.conf $DATADIR

						echo "shared_buffers = '$sb'" >> $DATADIR/postgresql.conf
						echo 'io_workers = 12' >> $DATADIR/postgresql.conf

						PATH=$BUILDSHP/$b/bin:$PATH_OLD

						pg_ctl -D $DATADIR -l $TS/pg.log start

						dropdb --if-exists test
						createdb test

						psql test -c "create $t table t1 (a int primary key, b int)"
						psql test -c "create $t table t2 (a int references t1(a), b int)"

						if [ "${order_1}" == "rand" ]; then
							psql test -c "insert into t1 select i, i from generate_series(1, ${rows_1}) s(i) order by random()"
						else
							psql test -c "insert into t1 select i, i from generate_series(1, ${rows_1}) s(i)"
						fi

						psql test -c "vacuum analyze"
						psql test -c "checkpoint"

						sed "s/ROWS/${rows_2}/" rand.template | sed "s/MAXID/${rows_1}/" > rand.sql
						sed "s/ROWS/${rows_2}/" seq.template | sed "s/MAXID/${rows_1}/" > seq.sql

						for r in $(seq 1 $RUNS); do

							for c in 1 8; do

								psql test -c "truncate t2"

								pgbench -n -M prepared -f seq.sql -j $c -c $c -T 10 test > tmp.log 2>&1

								tps=$(grep 'tps = ' tmp.log | awk '{print $3}')

								echo $rows_1 $rows_2 $sb $tt $b $order_1 seq $r $c $tps >> $TS/results.csv

								psql test -c "truncate t2"

								pgbench -n -M prepared -f seq.sql -j $c -c $c -T 10 test > tmp.log 2>&1

								tps=$(grep 'tps = ' tmp.log | awk '{print $3}')

								echo $rows_1 $rows_2 $sb $tt $b $order_1 rand $r $c $tps >> $TS/results.csv

							done

						done

						pg_ctl -D $DATADIR -l $TS/pg.log stop

					done

				done

			done

		done

	done

done