run-seqscan.sh

application/x-shellscript

Download

#!/usr/bin/env bash

set -e

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

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"
psql postgres -c "alter system set io_method = 'io_uring'"

pg_ctl -D $DATADIR -l pg.log restart

createdb $DBNAME

psql $DBNAME -c "CREATE EXTENSION pg_buffercache"
psql $DBNAME -c "CREATE EXTENSION pg_prewarm"

# prepare 64 tables, each ~1GB
for c in $(seq 0 63); do
	psql $DBNAME -c "create table test_${c} (a bigint, b text) with (fillfactor=10)"
	psql $DBNAME -c "insert into test_${c} select i, md5(i::text) from generate_series(1,1310000) s(i)"
	psql $DBNAME -c "vacuum freeze test_${c}"
	psql $DBNAME -c "checkpoint"
done


for pct in 60 75 90 90 100 110; do

	for c in 8 64 32 56 16 40 24 48; do

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

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

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

			sb=$((c*1024*pct/100))

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

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

			psql $DBNAME -c "select * from pg_settings" > $TS/$pct/$c/$b/settings.log 2>&1

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

				pgbench -n -c $c -j $c -P 1 -r -f select.sql -T 60 $DBNAME > $TS/$pct/$c/$b/$r.log 2>&1

				tps=$(grep '^tps = ' $TS/$pct/$c/$b/$r.log | awk '{print $3}')
				lat=$(grep '1000000000' $TS/$pct/$c/$b/$r.log | awk '{print $1}')
				lat2=$(grep 'pg_buffercache_evict_relation' $TS/$pct/$c/$b/$r.log | awk '{print $1}')

				echo $c $pct $b $sb $r $tps $lat $lat2 >> $TS/results.csv 2>&1

			done

		done

	done

done

pg_ctl -D $DATADIR -l pg.log stop