bench.sh

application/x-shellscript

Download

#!/bin/bash

PATH_OLD=$PATH

for l in unlogged logged; do

        for type in int bigint text; do

                for v in pg-master pg-patched-v2 pg-patched-v3; do

                        PATH=/var/lib/postgresql/$v/bin:$PATH_OLD

                        pg_ctl -D /mnt/data/pgdata -l pg.$v.log start
                        sleep 5

                        psql test -c "drop table if exists t"

                        if [ "$l" == "unlogged" ]; then
                                psql test -c "create unlogged table t (a $type)"
                        else
                                psql test -c "create table t (a $type)"
                        fi

                        if [ "$type" == "text" ]; then
                                psql test -c "insert into t select md5(random()::text) from generate_series(1,10000000)"
                        else
                                psql test -c "insert into t select 10000000 * random() from generate_series(1,10000000)"
                        fi

                        psql test -c "vacuum analyze"

                        psql test -c "checkpoint"

                        for r in `seq 1 8`; do

                                for m in 4MB 32MB 128MB; do

                                        psql test > tmp.log <<EOF
drop index if exists t_a_idx;
set maintenance_work_mem = '$m';
set max_parallel_maintenance_workers = 0;
\timing on
create index on t using hash (a);
EOF

                                        t=`grep Time tmp.log | awk '{print $2}'`

                                        echo $l $type $v $m $r $t >> results.csv 2>&1

                                        psql test -c "checkpoint"

                                done

                        done

                        pg_ctl -D /mnt/data/pgdata stop
                        sleep 5

                done

        done

done