pgbench-concurrent-resize-buffers.sh

#!/bin/bash

# The script requires three environment variables: a. BinDir points to the path
# containing all binaries, b. DataDir points to the path of data directory and
# c. ReproDir points to the path where postgresql.log will be placed.

function resize_shared_buffers
{
   local newsize=$1

   $BinDir/psql -d postgres << _EOF
show shared_buffers;
alter system set shared_buffers to '$newsize';
select pg_reload_conf();
select pg_sleep(1);
show shared_buffers;
_EOF
}

# Start server with default shared_buffers, expected 128MB
rm -f $DataDir/postgresql.auto.conf;
ServerLogFile=$ReproDir/postgresql.log
rm -f $ServerLogFile
$BinDir/pg_ctl -D $DataDir -l $ServerLogFile -w start

# Scale of 100 creates a database large enough to fill all the shared buffers
# when shared_buffers = '200MB'
$BinDir/pgbench -d postgres -p 5432 -i -I "dtgvpf" -s 100

pgbench_duration=150
# Start pgbench in the background
$BinDir/pgbench -d postgres -T $pgbench_duration -j 4 -c 5 --exit-on-abort -l &
# Given pgbench some time to spawn all the backends. We should remove this
# sleep at some point in time but right now the buffer resizing code doesn't
# work well with backend being started while resizing is going on.
sleep 10

# Keep resizing buffers till pgbench is running
# Two sets of sizes one for shrinking and one for expanding. Choose suitable
# one. At some point we should test mixture of shrinking and expanding
#sbsizes=(100MB 87MB 79MB 64MB 58MB 41MB 36MB 28MB 11MB 5MB)
sbsizes=(150MB 179MB 186MB 190MB 200MB 210MB 213MB 216MB 219MB)
sleep_duration=$(($pgbench_duration/${#sbsizes[@]}))

cnt=0
while [[ `jobs | grep pgbench | cut -d " " -f3` == "Running" ]]; do
    echo "pgbench is running"
    jobs | grep pgbench
    newsize=${sbsizes[$cnt]}
    echo "resizing shared_buffers to $newsize"
    resize_shared_buffers $newsize
    echo "sleeping for $sleep_duration seconds before next resizing"
    sleep $sleep_duration
    cnt=$((cnt+1))
done

if [ $cnt -lt ${#sbsizes[@]} ]; then
    echo "Finished $cnt iterations instead of ${#sbsizes[@]}"
else
    echo "All resizing iterations completed"
fi

echo "Check for any assertion failures in $ServerLogFile"

$BinDir/pg_ctl -D $DataDir -w stop