run-randomized.sh
application/x-shellscript
Filename: run-randomized.sh
Type: application/x-shellscript
Part: 0
#!/usr/bin/env bash
set -e
ROWS=$1
DEVICE=/dev/nvme0n1
# fraction of distinct values, a width of the fuzz interval
FUZZ=1
# number of intervals
CYCLES=100
# fillfactor of the table, to make the tuples look wider
FILLFACTOR=25
BUILDS="master patched"
function restart_postgres () {
pg_ctl -D /mnt/pgdata/data -l /mnt/pgdata/pg.log -w stop || true
pg_ctl -D /mnt/pgdata/data -l /mnt/pgdata/pg.log -w start
}
function drop_caches () {
sudo ./drop-caches.sh
}
function set_readahead() {
sudo blockdev --setra $1 $DEVICE
}
function check_postgres_build () {
command=$(pgrep -a postgres | grep pgdata)
if [[ ! $command =~ $1 ]]; then
exit 1
fi
}
if [ "$2" != "" ]; then
OUTDIR=`date +%Y%m%d-%H%M%S`-$2
else
OUTDIR=`date +%Y%m%d-%H%M%S`
fi
mkdir $OUTDIR
echo "build rows dataset relpages workers wm eic matches distinct run caching optimal readahead combine_limit timing" > $OUTDIR/results.csv
PATH_OLD=$PATH
PATH=/var/lib/postgresql/builds/pg-master/bin:$PATH_OLD
restart_postgres
for rows in $ROWS; do
dbname="test-$rows"
echo $dbname
dbexists=$(psql -t -A -c "select 1 from pg_database where datname = '$dbname'")
if [ "$dbexists" != "1" ]; then
createdb $dbname
for dataset in uniform uniform_pages linear linear_fuzz cyclic cyclic_fuzz; do
psql $dbname -c "create table $dataset (a bigint, b bigint, c text) with (fillfactor = $FILLFACTOR)"
if [ "$dataset" == "uniform" ]; then
distinct=$((rows/100))
psql $dbname -c "insert into $dataset select $distinct * random(), i, md5(random()::text) from generate_series(1, $rows) s(i)"
elif [ "$dataset" == "uniform_pages" ]; then
distinct=$((rows/24/100))
psql $dbname -c "insert into $dataset select a, b, c from (select a, b, c, generate_series(1,24) from (select a, b, c from (select ($distinct * random())::int a, i b, md5(random()::text) c from generate_series(1, $rows/24) s(i)) foo) bar) baz";
elif [ "$dataset" == "linear" ]; then
distinct=$((rows/100))
psql $dbname -c "insert into $dataset select ($distinct * (i * 1.0 / $rows)), i, md5(random()::text) from generate_series(1, $rows) s(i)"
elif [ "$dataset" == "linear_fuzz" ]; then
distinct=$((rows/100))
fuzz=$((distinct*FUZZ/100))
psql $dbname -c "insert into $dataset select ($distinct * (i * 1.0 / $rows)) + $fuzz * random(), i, md5(random()::text) from generate_series(1, $rows) s(i)"
elif [ "$dataset" == "cyclic" ]; then
cycle=$((rows/CYCLES))
distinct=$((rows/cycle))
psql $dbname -c "insert into $dataset select ($distinct * (mod(i,$cycle) * 1.0 / $cycle)), i, md5(random()::text) from generate_series(1, $rows) s(i)"
elif [ "$dataset" == "cyclic_fuzz" ]; then
cycle=$((rows/CYCLES))
distinct=$((rows/cycle))
fuzz=$((distinct*FUZZ/100))
psql $dbname -c "insert into $dataset select ($distinct * (mod(i,$cycle) * 1.0 / $cycle)) + $fuzz * random(), i, md5(random()::text) from generate_series(1, $rows) s(i)"
fi
psql $dbname -c "create index on $dataset (a)"
psql $dbname -c "vacuum analyze"
psql $dbname -c "checkpoint"
done
fi
for run in $(seq 1 3); do
# generate run parameters, randomize the order
rm -f parameters.txt
for dataset in uniform uniform_pages linear linear_fuzz cyclic cyclic_fuzz; do
if [ "$dataset" == "uniform" ]; then
distinct=$((rows/100))
elif [ "$dataset" == "uniform_pages" ]; then
distinct=$((rows/24/100))
elif [ "$dataset" == "linear" ]; then
distinct=$((rows/100))
elif [ "$dataset" == "linear_fuzz" ]; then
distinct=$((rows/100))
elif [ "$dataset" == "cyclic" ]; then
cycle=$((rows/CYCLES))
distinct=$((rows/cycle))
elif [ "$dataset" == "cyclic_fuzz" ]; then
cycle=$((rows/CYCLES))
distinct=$((rows/cycle))
fi
for workers in 0 4; do
for wm in 128 $((4*1024)) $((64*1024)); do
for eic in 0 1 8 16 32; do
for icl in 8 128; do
matches=1
while /bin/true; do
# did we match the whole dataset already?
if [[ $matches -gt $distinct ]]; then
break
fi
if [[ $((distinct/5)) -gt $matches ]]; then
matches=$((matches*2))
elif [[ $((distinct/5)) -lt 1 ]]; then
matches=$((matches+1))
else
matches=$((matches+distinct/5))
fi
for ra in 0 256; do
echo $dataset $workers $wm $eic $matches $distinct $ra $icl >> parameters.txt
done
done
done
done
done
done
done
sort -R parameters.txt > parameters.random
while IFS= read line; do
read -a params <<< "$line"
dataset="${params[0]}"
workers="${params[1]}"
wm="${params[2]}"
eic="${params[3]}"
matches="${params[4]}"
distinct="${params[5]}"
ra="${params[6]}"
icl="${params[7]}"
relpages=$(psql $dbname -t -A -c "select relpages from pg_class where relname = '$dataset'")
read -a BUILDS_ARRAY <<< $BUILDS
for build in $(shuf -e ${BUILDS_ARRAY[@]}); do
#for build in $BUILDS; do
PATH=/var/lib/postgresql/builds/pg-$build/bin:$PATH_OLD
echo $PATH
from=$(psql -t -A $dbname -c "SELECT (random() * ($distinct - $matches))::int")
to=$((from + matches))
# clean all caches (OS and postgres)
drop_caches
restart_postgres
set_readahead $ra
check_postgres_build $build
# is bitmap heap scan the optimal plan?
psql $dbname > explain.log 2>&1 <<EOF
SET max_parallel_workers_per_gather = $workers;
SET effective_io_concurrency = $eic;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET work_mem = '${wm}kB';
SET io_combine_limit = '${icl}kB';
EXPLAIN SELECT * FROM $dataset WHERE (a BETWEEN $from AND $to) OFFSET $rows;
EOF
bitmapscan=$(grep ' Bitmap Heap Scan' explain.log | wc -l)
seqscan=$(grep ' Seq Scan' explain.log | wc -l)
indexscan=$(grep ' Index Scan' explain.log | wc -l)
optimal="unknown"
if [ "$bitmapscan" == "1" ]; then
optimal="bitmapscan"
elif [ "$seqscan" == "1" ]; then
optimal="seqscan"
elif [ "$indexscan" == "1" ]; then
optimal="indexscan"
else
cat explain.log
fi
psql $dbname > timing.log 2>&1 <<EOF
SET enable_seqscan = off;
SET enable_indexscan = off;
SET max_parallel_workers_per_gather = $workers;
SET effective_io_concurrency = $eic;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET work_mem = '${wm}kB';
SET io_combine_limit = '${icl}kB';
EXPLAIN SELECT * FROM $dataset WHERE (a BETWEEN $from AND $to) OFFSET $rows;
\timing on
SELECT * FROM $dataset WHERE (a BETWEEN $from AND $to) OFFSET $rows;
EOF
t=`grep Time timing.log | awk '{print $2}'`
echo $build $rows $dataset $relpages $workers $wm $eic $matches $distinct $run uncached $optimal $ra $icl $t >> $OUTDIR/results.csv
echo "========== build: $build rows: $rows dataset: $dataset relpages: $relpages workers: $workers work_mem: $wm effective_io_concurrency: $eic matches: $matches distinct: $distinct run: $run caching: uncached optimal: $optimal readahead: $ra combine: $icl timing: $t ==========" >> $OUTDIR/explain.log 2>&1
cat timing.log >> $OUTDIR/explain.log 2>&1
# clean shared buffers (but keep OS cache)
restart_postgres
check_postgres_build $build
psql $dbname > timing.log 2>&1 <<EOF
SET enable_seqscan = off;
SET enable_indexscan = off;
SET max_parallel_workers_per_gather = $workers;
SET effective_io_concurrency = $eic;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET work_mem = '${wm}kB';
SET io_combine_limit = '${icl}kB';
EXPLAIN SELECT * FROM $dataset WHERE (a BETWEEN $from AND $to) OFFSET $rows;
\timing on
SELECT * FROM $dataset WHERE (a BETWEEN $from AND $to) OFFSET $rows;
EOF
t=`grep Time timing.log | awk '{print $2}'`
echo $build $rows $dataset $relpages $workers $wm $eic $matches $distinct $run cached-os $optimal $ra $icl $t >> $OUTDIR/results.csv
echo "========== build: $build rows: $rows dataset: $dataset relpages: $relpages workers: $workers work_mem: $wm effective_io_concurrency: $eic matches: $matches distinct: $distinct run: $run caching: cached-os optimal: $optimal readahead: $ra combine: $icl timing: $t ==========" >> $OUTDIR/explain.log 2>&1
cat timing.log >> $OUTDIR/explain.log 2>&1
# keep both caches
psql $dbname > timing.log 2>&1 <<EOF
SET enable_seqscan = off;
SET enable_indexscan = off;
SET max_parallel_workers_per_gather = $workers;
SET effective_io_concurrency = $eic;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET work_mem = '${wm}kB';
SET io_combine_limit = '${icl}kB';
EXPLAIN SELECT * FROM $dataset WHERE (a BETWEEN $from AND $to) OFFSET $rows;
\timing on
SELECT * FROM $dataset WHERE (a BETWEEN $from AND $to) OFFSET $rows;
EOF
t=`grep Time timing.log | awk '{print $2}'`
echo $build $rows $dataset $relpages $workers $wm $eic $matches $distinct $run cached $optimal $ra $icl $t >> $OUTDIR/results.csv
echo "========== build: $build rows: $rows dataset: $dataset relpages: $relpages workers: $workers work_mem: $wm effective_io_concurrency: $eic matches: $matches distinct: $distinct run: $run caching: cached optimal: $optimal readahead: $ra combine: $icl timing: $t ==========" >> $OUTDIR/explain.log 2>&1
cat timing.log >> $OUTDIR/explain.log 2>&1
done
done < parameters.random
done
done