run.sh

application/x-shellscript

Download

#!/usr/bin/env bash

set -e
set -x

DATASET=$1
ROWS=$2
FILLFACTOR=$3
LENGTH=$4
HOST=$(hostname)

DATADIR=/mnt/raid-nvme/data-konstantin
PATH_OLD=$PATH

killall -9 postgres || true
sleep 1

rm -Rf $DATADIR #*.csv *.log

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

pg_ctl -D $DATADIR init

echo "shared_buffers = 4GB" >> $DATADIR/postgresql.conf
echo "io_method = io_uring" >> $DATADIR/postgresql.conf
echo "max_wal_size = 32GB" >> $DATADIR/postgresql.conf

pg_ctl -D $DATADIR -l pg.log start

createdb test

psql test -c "create table t (pk integer primary key, payload text default repeat('x', $LENGTH)) with (fillfactor=$FILLFACTOR)"

if [ "$DATASET" == "sequential" ]; then
	psql test -c "insert into t select i from generate_series(1,$ROWS) s(i)"
elif [ "$DATASET" == "random" ]; then
	psql test -c "insert into t select i from generate_series(1,$ROWS) s(i) order by random()"
else
	echo "invalid dataset"
	exit 1
fi

psql test -c "vacuum freeze"
psql test -c "analyze"

pages=$(psql test -t -A -c "select relpages from pg_class where relname = 't'")

pg_ctl -D $DATADIR -l pg.log stop


for r in $(seq 1 5); do

	for p in master patched-v5 patched-v5-threshold; do

		export PATH=/home/tomas/builds/$p/bin:$PATH_OLD

		numactl --physcpubind=1 pg_ctl -D $DATADIR -l pg.log start

		echo "\set pk random(1, $ROWS)" > script.sql
		echo "set effective_io_concurrency = 10;" >> script.sql

		if [ "$p" == "patched-v5-threshold" ]; then
			echo "set read_stream_threshold = 10;" >> script.sql
		fi

		echo "select * from t where pk >= :pk order by pk limit 10;" >> script.sql

		numactl --physcpubind=1 pgbench -n -M prepared -f script.sql -T 60 test > tmp.log 2>&1

		for eic in 1 4 16 64 256 1000; do

			for limit in 1 4 16 64 256 1000; do

				echo "\set pk random(1, $ROWS)" > script.sql
				echo "set effective_io_concurrency = $eic;" >> script.sql

				if [ "$p" == "patched-v5-threshold" ]; then
					echo "set read_stream_threshold = 10;" >> script.sql
				fi

				echo "select * from t where pk >= :pk order by pk limit $limit;" >> script.sql

				numactl --physcpubind=1 pgbench -n -M prepared -f script.sql -T 15 test > tmp.log 2>&1

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

				echo $HOST $DATASET $ROWS $FILLFACTOR $LENGTH $pages $r $eic $limit $p $tps $lat >> results-$ROWS-$FILLFACTOR.csv

			done

		done

		pg_ctl -D $DATADIR -l pg.log stop

	done

done