run-pgbench.sh

application/x-sh

Download

#!/bin/bash

LABEL=$1
SCALE=$2
NRUNS=5
DURATION=30

function run_pgbench {

	script=$1
	mode=$2
	indexes=$3
	scale=$4
	run=$5

	pgbench -n -f $script.sql -M $mode -T $DURATION test > pgbench.log 2>&1 &

	# assign pgbench to CPU #1
	for p in `ps ax | grep pgbench | grep $script | awk '{print $1}'`; do
		taskset -p 1 $p >> debug.log 2>&1
	done

	# assign postgres backend to CPU #2
	for p in `ps ax | grep 'postgres test' | grep 'postgres:' | awk '{print $1}'`; do
		taskset -p 2 $p >> debug.log 2>&1
	done

	# wait for all processes (including the pgbench) to complete
	wait

	tps=`grep excluding pgbench.log | awk '{print $3}'`
	echo $LABEL $script $mode $indexes $scale $run $tps
}

echo '\set aid random(1, 100000 * :scale)' > select.sql
echo 'SELECT abalance FROM pgbench_accounts WHERE aid = :aid;' >> select.sql

echo '\set aid random(1, 100000 * :scale)' > order-by.sql
echo 'SELECT abalance FROM pgbench_accounts WHERE aid = :aid ORDER BY aid, bid;' >> order-by.sql

echo '\set aid random(1, 100000 * :scale)' > explain.sql
echo 'EXPLAIN SELECT abalance FROM pgbench_accounts WHERE aid = :aid;' >> explain.sql

echo '\set aid random(1, 100000 * :scale)' > explain-order-by.sql
echo 'EXPLAIN SELECT abalance FROM pgbench_accounts WHERE aid = :aid ORDER BY aid, bid;' >> explain-order-by.sql

echo '\set aid random(1, 100000 * :scale)' > select2.sql
echo 'SELECT abalance, bbalance, tbalance FROM pgbench_accounts JOIN pgbench_branches USING (bid) JOIN pgbench_tellers USING (bid) WHERE aid = :aid;' >> select2.sql

echo '\set aid random(1, 100000 * :scale)' > order-by2.sql
echo 'SELECT abalance, bbalance, tbalance FROM pgbench_accounts JOIN pgbench_branches USING (bid) JOIN pgbench_tellers USING (bid) WHERE aid = :aid ORDER BY aid, bid;' >> order-by2.sql

echo '\set aid random(1, 100000 * :scale)' > explain2.sql
echo 'EXPLAIN SELECT abalance, bbalance, tbalance FROM pgbench_accounts JOIN pgbench_branches USING (bid) JOIN pgbench_tellers USING (bid) WHERE aid = :aid;' >> explain2.sql

echo '\set aid random(1, 100000 * :scale)' > explain-order-by2.sql
echo 'EXPLAIN SELECT abalance, bbalance, tbalance FROM pgbench_accounts JOIN pgbench_branches USING (bid) JOIN pgbench_tellers USING (bid) WHERE aid = :aid ORDER BY aid, bid;' >> explain-order-by2.sql

echo "setting cpupower" >> debug.log 2>&1
sudo cpupower -c all frequency-set -g performance >> debug.log 2>&1
sudo cpupower -c all frequency-set -d 2000MHz >> debug.log 2>&1
sudo cpupower -c all frequency-set -u 2000MHz >> debug.log 2>&1

dropdb test >> debug.log 2>&1
createdb test >> debug.log 2>&1

pgbench -i -s $SCALE test >> debug.log 2>&1
psql test -c 'vacuum analyze' >> debug.log 2>&1
psql test -c 'checkpoint' >> debug.log 2>&1

for r in `seq 1 $NRUNS`; do

	for x in select order-by explain explain-order-by select2 order-by2 explain2 explain-order-by2; do

		run_pgbench $x simple no $SCALE $r

		run_pgbench $x prepared no $SCALE $r

	done

done

psql test >> debug.log 2>&1 <<EOF
create index on pgbench_accounts (aid,bid);
create index on pgbench_accounts (bid,aid);
create index on pgbench_accounts (bid,aid,abalance);
create index on pgbench_accounts (aid,bid,abalance);
create index on pgbench_accounts (abalance,aid,bid);
create index on pgbench_accounts (abalance,bid,aid);
create index on pgbench_accounts (bid,abalance,aid);
create index on pgbench_accounts (bid,abalance,aid);

create index on pgbench_branches (bid, bbalance);
create index on pgbench_branches (bbalance, bid);

create index on pgbench_tellers (tid, tbalance);
create index on pgbench_tellers (tbalance, tid);

vacuum analyze;
checkpoint;
EOF

for r in `seq 1 $NRUNS`; do

	for x in select order-by explain explain-order-by select2 order-by2 explain2 explain-order-by2; do

		run_pgbench $x simple yes $SCALE $r

		run_pgbench $x prepared yes $SCALE $r

	done

done