Script for performance benchmarks for https://www.postgresql.org/message-id/flat/CALdSSPgahNUD_%3DpB_j%3D1zSnDBaiOtqVfzo8Ejt5J_k7qZiU1Tw%40mail.gmail.com

avg.kt

import java.nio.file.Files
import java.nio.file.Paths
import kotlin.io.path.name

fun main(args: Array<String>) {

    val benchmarksDir = "/home/nkey/pg/pg_bench"
    val avgBenchmarksDir = "/home/nkey/pg/pg_bench/avg"
    val versionToRuns: MutableMap<String, MutableList<List<Long>>> = mutableMapOf()

    for (file in Files.list(Paths.get(benchmarksDir)).filter { it.fileName.name.startsWith("version") }) {

        if (!file.fileName.name.endsWith("_raw")) continue;
        val size =
            if (file.fileName.name.contains("vanilla")) ("vanilla") else file.fileName.name.replace("version_size_", "")
                .substringBefore("_")
        val results = Files.readAllLines(file).map { it.toLong() }

        versionToRuns.computeIfAbsent(size) { mutableListOf() }.add(results)
    }

    for (sizeRuns in versionToRuns) {
        val version = sizeRuns.key
        val sum: MutableList<Long> = mutableListOf()
        for (i in 1..180) {
            sum.add(0)
        }

        for (run in sizeRuns.value) {
            var i = 0
            for (sec in run) {
                sum[i++] += sec
            }
        }

        val avg = sum.map { it / sizeRuns.value.size }.map { it.toString() }
        Files.write(Paths.get(avgBenchmarksDir, "${version}_avg"), avg)
    }
}

build-postgres.sh

#!/bin/bash

sudo apt update
sudo apt upgrade -y

sudo apt-get -y install build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libxml2-utils xsltproc ccache

git clone https://github.com/postgres/postgres.git
cd postgres/

# version for development
# sudo apt install libipc-run-perl
#./configure --prefix=/home/${USER}/pg/install --enable-cassert --enable-debug CFLAGS="-fno-omit-frame-pointer -no-pie" --enable-tap-tests

# version for benchmark
./configure --prefix=/home/${USER}/pg/install CFLAGS="-fno-omit-frame-pointer -no-pie"

make
make install

init-primary.sh

sudo chown $USER /mnt/
~/pg/install/bin/initdb -D /mnt/pgdata -U postgres
echo "max_connections=5000" >> /mnt/pgdata/postgresql.auto.conf
echo "fsync=off" >> /mnt/pgdata/postgresql.auto.conf
echo "listen_addresses='*'" >> /mnt/pgdata/postgresql.auto.conf

echo "host    replication     all             0.0.0.0/0               trust" >> /mnt/pgdata/pg_hba.conf

../pg/install/bin/postgres -D /mnt/pgdata

init-ssh.sh

nano ~/.ssh/id_rsa
#past your key
chmod 600 ~/.ssh/id_rsa

init-standby.sh

export PRIMARY_IP=10.0.0.4
sudo chown $USER /mnt/
scp -r $PRIMARY_IP:/mnt/pgdata /mnt

echo "primary_conninfo = 'user=postgres host=$PRIMARY_IP port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgrestarget_session_attrs=any'" > /mnt/pgdata/postgresql.auto.conf
echo "fsync=off" >> /mnt/pgdata/postgresql.auto.conf
echo "max_connections=5000" >> /mnt/pgdata/postgresql.auto.conf
echo "listen_addresses='*'" >> /mnt/pgdata/postgresql.auto.conf
echo "hot_standby_feedback = on" >> /mnt/pgdata/postgresql.auto.conf

echo "host    replication     all             0.0.0.0/0               trust" >> /mnt/pgdata/pg_hba.conf
echo "host    all             all             0.0.0.0/0               trust" >> /mnt/pgdata/pg_hba.conf

touch /mnt/pgdata/standby.signal

../pg/install/bin/postgres -D /mnt/pgdata

multirun.sh

#!/bin/bash

export PRIMARY_IP=10.0.0.4
export STANDBY_IP=10.0.0.5

for i in {1..5}
do
   echo "Loop number $"
   echo "Vanilla test"


   ssh $USER@$STANDBY_IP "cd ~/postgres ; git reset --hard"
   ssh $USER@$STANDBY_IP "cd ~/postgres ; make & make install"

   ./run.sh vanilla $i


   for s in 10000 20000 40000 80000 150000 250000 1000000 2000000
   do
      echo "Limit is $s"

      ssh $USER@$STANDBY_IP "cd ~/postgres ; git reset --hard; git apply WIP.patch"
      ssh $USER@$STANDBY_IP "sed -i 's/1111111/$s/g' ~/postgres/src/backend/storage/ipc/procarray.c"
      ssh $USER@$STANDBY_IP "cd ~/postgres ; make & make install"

      ./run.sh size_$s $i

   done
done

run.sh

export PRIMARY_IP=10.0.0.4
export STANDBY_IP=10.0.0.5
export RUN_LOG=version_$1_run_$2.log

echo "Restart primary"
screen -d -m ~/pg/install/bin/pg_ctl restart -D /mnt/pgdata

echo "Restart standby"
ssh $USER@$STANDBY_IP 'screen -d -m ~/pg/install/bin/pg_ctl restart -D /mnt/pgdata'

#make sure standby is up to date
sleep 30

echo "Init database"
~/pg/install/bin/pgbench -i -s 10 -U postgres -d postgres

sleep 10

echo "Warm up primary"
~/pg/install/bin/pgbench -b simple-update -j 2 -c 16 -P 1 -T 10 -U postgres postgres

echo "Warm up standby"
~/pg/install/bin/pgbench -h $STANDBY_IP -b select-only -j 2 -c 16 -n -P 1 -T 10 -U postgres postgres

echo "Starting pgbench on primary"
#screen -d -m ~/pg/install/bin/pgbench -b simple-update -j 4 -c 16 -P 1 -T 180 -U postgres postgres
screen -d -m ~/pg/install/bin/pgbench -f ~/txid_current.bench -j 4 -c 64 -P 1 -T 180 -U postgres postgres

echo "Starting pgbench on stanbdy"
#ssh $USER@$STANDBY_IP "screen -d -m -L -Logfile $RUN_LOG ~/pg/install/bin/pgbench -n -b select-only -j 4 -c 16 -P 1 -T 180 -U postgres postgres"
ssh $USER@$STANDBY_IP "screen -d -m -L -Logfile $RUN_LOG ~/pg/install/bin/pgbench -n -f ~/select1.bench -j 4 -c 64 -P 1 -T 180 -U postgres postgres"

echo "Waiting 60 sec..."
sleep 60

echo "Starting long transaction..."
~/pg/install/bin/psql -c "BEGIN; select txid_current();SELECT pg_sleep(30);COMMIT;" -U postgres postgres

echo "commited."
sleep 100

ssh $USER@$STANDBY_IP "cat $RUN_LOG | grep progress | sed 's/\progress: .* s, \b//g' | sed 's/\..*\b//g' > ${RUN_LOG}_raw"

echo "$RUN_LOG is ready"

~/pg/install/bin/psql -c "CHECKPOINT;" -U postgres postgres
~/pg/install/bin/psql -h $STANDBY_IP -c "CHECKPOINT;" -U postgres postgres

sleep 10

runs.sh

#!/bin/bash
# should be run on primary server

./run.sh $1 1
sleep 10
./run.sh $1 2
sleep 10
./run.sh $1 3
sleep 10

select1.bench

BEGIN;
SELECT 1;
END;

txid_current.bench

BEGIN;
SELECT txid_current();
END;

ereport(LOG, errmsg("compressing xids"));