benchmark_postgres.sh

#!/bin/bash
 
POSTGRES_VERSION='9.5'
 
# With how many clients to do this test
CLIENTS="20"
 
# Duration of each test
DURATION=3600
 
PGBENCH="/usr/pgsql-${POSTGRES_VERSION}/bin/pgbench"
 
# fail on any errors
set -e
 
echot(){
    echo -en "$(date '+%F %T') $@"
}
 
wait_for_postgres(){
    while ! /usr/pgsql-${POSTGRES_VERSION}/bin/pg_isready -U postgres -d pgbench -q; do
        sleep 1
    done
}
 
for num in $CLIENTS; do
    for bench_type in '-N' '' '-S'; do
        echot "Running VACUUM FULL ANALYZE... "
        psql -q -U postgres -d pgbench -c 'VACUUM FULL ANALYZE'
        echo Done
 
        echot "Stopping postgres..."
        systemctl stop postgresql-${POSTGRES_VERSION}
        echo Done
 
        echot "Dropping all page caches... "
        echo 3 > /proc/sys/vm/drop_caches
        echo Done
 
        echot "Starting postgres..."
        systemctl start postgresql-${POSTGRES_VERSION}
        wait_for_postgres
        echo Done
 
        echot "Running pgbench:\n"
        echo "$PGBENCH $bench_type -U postgres -c $num -j $num -T $DURATION pgbench"
        $PGBENCH $bench_type -U postgres -c $num -j $num -T $DURATION pgbench
        echo
    done
    echo -e "#####################################################################\n"
done