bench_numa.sh

application/x-shellscript

Download

#!/bin/bash

#set -e

TS=$(date +%Y%m%d-%H%M%S)
DURATION=60
#DURATION=1
DATADIR="/db/pg19.numa"

pkill -9 postgres || true
sleep 1

mkdir numa-$TS
PATHORG=$PATH

for binary in pgproc4 pgproc16 master sweep4 sweep16 numa4 numa16; do
	PGDIR="/usr/pgsql19.$binary"
	if [ ! -d $PGDIR ]; then
		echo "$PGDIR does not exist"
		exit;
	fi
done

#first time only
#rm -rf $DATADIR
#PATH="/usr/pgsql19.master/bin:$PATHORG"
#initdb -D $DATADIR
#echo "shared_buffers='32GB'" > $DATADIR/postgresql.auto.conf
#echo "max_wal_size='8GB'" >> $DATADIR/postgresql.auto.conf
#echo "wal_level='minimal'" >> $DATADIR/postgresql.auto.conf
#echo "max_wal_senders=0" >> $DATADIR/postgresql.auto.conf

# minimal
#pg_ctl -D $DATADIR -l numa-$TS/init_pg_startup.log start
#pgbench -i -s 2000 | tee numa-$TS/init_pgbench.log
#psql -c '\l+' | tee numa-$TS/size.log
#pg_ctl -D $DATADIR stop

#for binary in pgproc4 pgproc16 master numa4 numa16 sweep4 sweep16; do
for binary in master pgproc4 pgproc16; do
	PGDIR="/usr/pgsql19.$binary"
	PATH="$PGDIR/bin:$PATHORG"

	sync; sync; sync
	echo 3 | sudo tee /proc/sys/vm/drop_caches

	for connections in 1 8 64 128 1024; do
	#for connections in 128; do
		for huge_pages in on off; do
			LOGPREFIX="numa-$TS/${binary}__${connections}__${huge_pages}"
			echo $LOGPREFIX

			cat > $DATADIR/postgresql.auto.conf << EOF
shared_buffers='31 GB'
huge_pages='${huge_pages}'
max_connections='10000'
wal_buffers='1024 MB'
max_prepared_transactions = 1000
max_parallel_workers = 128
fsync = 'off'
io_workers = 8
EOF
			echo $binary | grep -q numa
			if [ $? -eq 0 ]; then
				echo NUMA buffers
				echo "debug_numa = 'buffers'" >> $DATADIR/postgresql.auto.conf
			fi
			echo $binary | grep -q pgproc
			if [ $? -eq 0 ]; then
				echo NUMA+proc buffers
				echo "debug_numa = 'buffers,procs'" >> $DATADIR/postgresql.auto.conf
			fi

			pg_ctl -D $DATADIR -l ${LOGPREFIX}_pg.log start

			# warmup,each time we are restarting the backends need to fill s_b from VFS cache 
			# (with small conn counts it would take way too long to populate s_b, but i didnt want to use prewarm as it would place everything on node))
			pgbench -n -S -j 64 -c 64 -T 30 > ${LOGPREFIX}_warmup.log 2>&1

			# run the main workload
			pgbench -n -S -j $connections -c $connections -T $DURATION > ${LOGPREFIX}_pgbench.log 2>&1

			pg_ctl -D $DATADIR -l ${LOGPREFIX}_pg.log stop

			# just in case
			sleep 1
			pkill postgres
			pkill -9 postgres

			sleep 1

		done
	done
done