Benchmark script to measure wal2json performance

benchmark_results.txt

# With appendStringInfoStrings

wal2json version:   7da3e5060acaa5615d20f390ecc5eedb0e62d116
pg version:         PostgreSQL 11.0 (Debian 11.0-1.pgdg90+2)    
pgbench scale:      5
pgbench txns:       50000
pgbench throughput: 1053.523630
wal size:           218103808
slot read realtime: 0m9.017s        
slot read systime:  0m4.432s      
slot options        -o write-in-chunks=1 -o include-schemas=1 -o include-types=1
output size:        233042669
output lines:       850057                                       
output md5:         23c9d51959deaeac30952b1015ddf863

# On master

wal2json version:   aa4077a6e05ae3ba2995172623b9928a190f1690
pg version:         PostgreSQL 11.0 (Debian 11.0-1.pgdg90+2)
pgbench scale:      5
pgbench txns:       50000
pgbench throughput: 1058.807863
wal size:           218103808
slot read realtime: 0m11.240s
slot read systime:  0m5.792s
slot options        -o write-in-chunks=1 -o include-schemas=1 -o include-types=1
output size:        233042643
output lines:       850057
output md5:         57ccef6173d3edc8040a10786371d27a

benchmark_wal2json.sh

#!/usr/bin/env bash

set -o nounset -o pipefail -o errexit

PGDATABASE=pgbench
SCALE=${PGBENCH_SCALE:-5}
TRANSACTIONS=${PGBENCH_TRANSACTIONS:-100000}
CONCURRENCY=${PGBENCH_CONCURRENCY:-1}
OUTFILE=benchmark_output.json
SEED=${PGBENCH_SEED:-1}

rm -f $OUTFILE

createdb $PGDATABASE

REPLICA_IDENTITY="FULL"

READ_OPTIONS="-o write-in-chunks=1 \
  -o include-schemas=1 \
  -o include-types=1 \
  "

pgbench -q -i -s $SCALE $PGDATABASE 2>/dev/null

psql -q -d pgbench -t <<-SQL
  ALTER TABLE pgbench_history ADD COLUMN pk BIGSERIAL PRIMARY KEY;
  ALTER TABLE pgbench_history REPLICA IDENTITY $REPLICA_IDENTITY;
  ALTER TABLE pgbench_tellers REPLICA IDENTITY $REPLICA_IDENTITY;
  ALTER TABLE pgbench_accounts REPLICA IDENTITY $REPLICA_IDENTITY;
  ALTER TABLE pgbench_branches REPLICA IDENTITY $REPLICA_IDENTITY;
SQL

psql -q -Atc "CHECKPOINT" -o /dev/null $PGDATABASE

pg_recvlogical -d $PGDATABASE --slot test_slot_a --create-slot -P wal2json
pg_recvlogical -d $PGDATABASE --slot test_slot_b --create-slot -P wal2json

tps=$(pgbench -c $CONCURRENCY -t $TRANSACTIONS -n $PGDATABASE --random-seed=$SEED | grep excluding | awk '{print $3}')

psql -d $PGDATABASE -o /dev/null -t <<-SQL
  BEGIN;
  DELETE FROM pgbench_history;
  DELETE FROM pgbench_tellers;
  DELETE FROM pgbench_accounts;
  DELETE FROM pgbench_branches;
  COMMIT;
SQL

psql -t -c "CHECKPOINT" -o /dev/null $PGDATABASE

WAL_SIZE=$(psql -d $PGDATABASE -Atc "SELECT sum(size) FROM pg_ls_waldir()")
END_LSN=$(psql -d $PGDATABASE -Atc "SELECT * FROM pg_current_wal_lsn()")

PRETTY_WAL_SIZE=$(psql -d $PGDATABASE -Atc "SELECT pg_size_pretty($WAL_SIZE::bigint)")

# read time
readtimes=$(bash -c "time pg_recvlogical -s 1 -d $PGDATABASE  --slot test_slot_a -E $END_LSN --start $READ_OPTIONS -f /dev/null" 2>&1)
realtime=$(echo $readtimes | awk '{print $2}')
systime=$(echo $readtimes | awk '{print $6}')

pg_recvlogical -s 1 -d $PGDATABASE --slot test_slot_b -E $END_LSN --start $READ_OPTIONS -f $OUTFILE

cat << EOF

wal2json version:   $(git rev-parse HEAD)
pg version:         $(pg_config --version)
pgbench scale:      $SCALE
pgbench txns:       $TRANSACTIONS
pgbench throughput: $tps
wal size:           $WAL_SIZE
slot read realtime: $realtime
slot read systime:  $systime
slot options        $(echo $READ_OPTIONS | tr '\n' ' ' | sed 's/\s\+/ /g')
output size:        $(stat --printf="%s" $OUTFILE)
output lines:       $(wc -l < $OUTFILE)
output md5:         $(md5sum $OUTFILE | awk '{print $1}')
EOF