copy.sh

application/x-shellscript

Download

#!/usr/bin/env bash

PATH_OLD=$PATH
BUILDDIR=/home/tomas/builds
BUILDS="master copy"

DATADIR=/mnt/raid-nvme/data-copy

RUNS=100

for nrows in 10 100 1000 10000 10000 100000 1000000; do

	for b in $BUILDS; do

		export PATH=$BUILDDIR/$b/bin:$PATH_OLD

		killall -9 postgres || true
		rm -Rf $DATADIR

		sleep 1

		pg_ctl -D $DATADIR init

		echo "shared_buffers = 1GB" >> $DATADIR/postgresql.conf
		echo "max_wal_size = 128GB" >> $DATADIR/postgresql.conf

		pg_ctl -D $DATADIR -l pg.log start

		for fmt in text csv binary; do

			for cols in 1 10 100; do

				create="create unlogged table t (c1 int"
				insert="insert into t select i"
				for c in $(seq 2 $cols); do
					create="$create, c$c int"
					insert="$insert, i"
				done
				create="$create)"
				insert="$insert from generate_series(1,$nrows) s(i)"

				createdb test
				psql test -c "$create"
				psql test -c "$insert"
				psql test -c "vacuum freeze"
				psql test -c "vacuum analyze"

				for r in $(seq 1 $RUNS); do

					psql test > copy.log <<EOF
select 'START', extract(epoch from now());
copy t to '/tmp/copy.data' with (format $fmt);
select 'END', extract(epoch from now());
EOF

					t1=$(grep 'START' copy.log | awk '{print $3}')
					t2=$(grep 'END' copy.log | awk '{print $3}')

					t=$(echo "1000 * ($t2 - $t1)" | bc)

					cat copy.log

					echo $nrows $b $fmt $cols TO $r $t >> results.csv 2>&1

				done

				psql test -c "truncate t"

				for r in $(seq 1 $RUNS); do

					psql test -c "truncate t"

					psql test > copy.log <<EOF
select 'START', extract(epoch from now());
copy t from '/tmp/copy.data' with (format $fmt);
select 'END', extract(epoch from now());
EOF

					t1=$(grep 'START' copy.log | awk '{print $3}')
					t2=$(grep 'END' copy.log | awk '{print $3}')

					t=$(echo "1000 * ($t2 - $t1)" | bc)

					cat copy.log

					echo $nrows $b $fmt $cols FROM $r $t >> results.csv 2>&1

				done

				dropdb test

			done

		done

		pg_ctl -D $DATADIR -l pg.log stop

	done

done