SSB 100 performance testing on PostgreSQL 12 and Ubuntu 20.04

SSB 100 performance testing on PostgreSQL 12 and Ubuntu 20.04

#pg_bulkload
sudo apt install libcrypto++6 libssl-dev libkrb5-dev libselinux-dev libpam-dev libcrypto++-dev libgssapi-krb5-2 libz-dev libedit-dev

#note - dbgen segfaults on ARM, fixed by using gcc-10
sudo apt install gcc-10

#dexter and build basics
sudo apt-get install gcc make flex bison byacc git ruby ruby-dev postgresql-server-dev-all

pushd /tmp
git clone https://github.com/grobbie/ssb-dbgen
pushd ssb-dbgen
make
./dbgen -s 10 -T s -f
./dbgen -s 10 -T d -f
./dbgen -s 10 -T p -f
./dbgen -s 10 -T c -f
./dbgen -s 10 -T l -f
mv *.tbl /tmp
popd

curl -L \
https://github.com/HypoPG/hypopg/archive/1.3.1.tar.gz \
| tar xz
pushd hypopg-1.3.1
make
sudo make install
sudo gem install pgdexter
popd

wget https://github.com/ossc-db/pg_bulkload/archive/refs/tags/VERSION3_1_19.tar.gz
tar xzf VERSION3_1_19.tar.gz
pushd pg_bulkload-VERSION3_1_19
make
sudo make install
popd

for t in lineorder date customer part supplier; do
cat <<-EOF | tee $t.ctl
OUTPUT = $t
INPUT = /tmp/$t.tbl
TYPE = CSV
QUOTE = "\""
DELIMITER = "|"
EOF
done

git clone https://github.com/nuko-yokohama/ssb-postgres.git
pushd ssb-postgres
sudo -u postgres createdb ssb
sudo -u postgres psql ssb -f tables.sql
sudo -u postgres psql ssb -c "CREATE EXTENSION pg_bulkload"
sudo -u postgres /usr/lib/postgresql/12/bin/pg_bulkload -d ssb ../ssb-dbgen/lineorder.ctl
sudo -u postgres /usr/lib/postgresql/12/bin/pg_bulkload -d ssb ../ssb-dbgen/date.ctl
sudo -u postgres /usr/lib/postgresql/12/bin/pg_bulkload -d ssb ../ssb-dbgen/supplier.ctl
sudo -u postgres /usr/lib/postgresql/12/bin/pg_bulkload -d ssb ../ssb-dbgen/part.ctl
sudo -u postgres /usr/lib/postgresql/12/bin/pg_bulkload -d ssb ../ssb-dbgen/customer.ctl
sudo -u postgres psql ssb -c "ANALYZE VERBOSE"
cat explain.sql | sed -e "s/\-\-\ Q[0-9]\.[0-9]/select \'--query-- &\' as query;/" | sed -e "s/EXPLAIN\ VERBOSE/select concat(\'--start-- \', extract(epoch from now())) as execution_start;/"
| sudo -u postgres tee queries.sql
sudo -u postgres dexter ssb queries.sql --create --input-format sql

total_cost=0
begin_time=$(date +%s.%N)
echo "begin run - $begin_time"
sudo -u postgres psql ssb -f queries.sql | sudo -u postgres tee results.txt
end_time=$(date +%s.%N)
cost=$(echo "$end_time-$begin_time"|bc)
total_cost=$(echo "$total_cost+$cost"|bc)
echo "run succ, cost: $cost, totalCost: $total_cost, $(date)"