test-toast.sh

application/x-shellscript

Download

#!/usr/bin/bash

set -e

PRIMARY=/mnt/pgdata2/data-primary
REPLICA=/mnt/pgdata2/data-replica

killall -9 postgres || true

rm -Rf $PRIMARY
rm -Rf $REPLICA

rm -f primary.log replica.log

pg_ctl -D $PRIMARY init
pg_ctl -D $REPLICA init

echo "wal_level = logical" >> $PRIMARY/postgresql.conf 2>&1
echo "max_wal_size = '32GB'" >> $PRIMARY/postgresql.conf 2>&1

echo "port = 5433" >> $REPLICA/postgresql.conf 2>&1
echo "max_wal_size = '32GB'" >> $REPLICA/postgresql.conf 2>&1

pg_ctl -D $PRIMARY -l primary.log start
pg_ctl -D $REPLICA -l replica.log start

RESULTS=results-toast-$(date +%s).csv

createdb test
createdb -p 5433 test

for rows in 100000 10000 1000000; do

	repeats=$((100000000/rows))

	# each string value has length 32
	size=$((rows*repeats*32))
	max=$((100*1024*1024*1024))

	echo "GENERATED SIZE: $size"

	for toast in pglz lz4; do

		#for wm in 64kB 512kB 4MB; do
		for wm in 4MB; do

			for dist in random2 random compressible; do

				for compression in off pglz lz4; do

					echo "logical_decoding_work_mem = '$wm'" >> $PRIMARY/postgresql.conf 2>&1

					pg_ctl -D $PRIMARY -l primary.log restart
					pg_ctl -D $REPLICA -l replica.log restart

					psql test -c "drop table if exists test_table"
					psql test -p 5433 -c "drop table if exists test_table"

					psql test -c "create table test_table (id int, val text)"
					psql test -p 5433 -c "create table test_table (id int, val text)"

					psql test -c "create publication p for table test_table"
					psql test -p 5433 -c "create subscription s connection 'host=localhost port=5432 user=tomas dbname=test' publication p with (spill_compression = '$compression')"

					echo "set default_toast_compression = '$toast';" > insert.sql

					if [ "$dist" == "random" ]; then
						v=$(psql -t -A test -c "select string_agg(v, '') from (select md5(i::text) as v from generate_series(1,$repeats) s(i)) foo")
						v="'$v'"

						echo "insert into test_table select i, $v from generate_series(1, $rows) s(i);" >> insert.sql
					elif [ "$dist" == "random2" ]; then
						echo "insert into test_table select mod(i, $rows), string_agg(md5(i::text), '') from generate_series(1, $rows*$repeats) s(i) group by mod(i, $rows)" >> insert.sql
					else
						v="repeat(md5(i::text), $repeats)"

						echo "insert into test_table select i, $v from generate_series(1, $rows) s(i);" >> insert.sql
					fi

					s=$(date +%s)

					psql test -c "truncate test_table"
					cat insert.sql | psql test

					while /bin/true; do

						c=$(psql test -p 5433 -t -A -c "select count(*) from test_table")

						if [ "$c" == "$rows" ]; then
							break
						fi

						echo "wait"
						sleep 1

					done

					e=$(date +%s)

					bytes=$(psql -t -A test -c "select spill_bytes from pg_stat_replication_slots")

					if [ "$compression" == "off" ]; then
						psql -t -A test -c "copy test_table to '/mnt/pgdata2/toast-$wm-$toast-$compression-$rows-$repeats-$dist.dump'";
						compsize=$(ls -l /mnt/pgdata2/toast-$wm-$toast-$compression-$rows-$repeats-$dist.dump | awk '{print $5}')
					elif [ "$compression" == "pglz" ]; then
						psql -t -A test -c "copy test_table to program 'gzip -1 -c > /mnt/pgdata2/toast-$wm-$toast-$compression-$rows-$repeats-$dist.dump.gz'";
						compsize=$(ls -l /mnt/pgdata2/toast-$wm-$toast-$compression-$rows-$repeats-$dist.dump.gz | awk '{print $5}')
					elif [ "$compression" == "lz4" ]; then
						psql -t -A test -c "copy test_table to program 'lz4 -1 -c > /mnt/pgdata2/toast-$wm-$toast-$compression-$rows-$repeats-$dist.dump.lz4'";
						compsize=$(ls -l /mnt/pgdata2/toast-$wm-$toast-$compression-$rows-$repeats-$dist.dump.lz4 | awk '{print $5}')
					fi

					echo $s $e $wm $toast $compression $rows $repeats $dist $((e-s)) $bytes $size $compsize >> $RESULTS 2>&1

					psql test -p 5433 -c "drop subscription s"
					psql test -c "drop publication p"

				done

			done

		done

	done

done