test.sh

application/x-shellscript

Download

#!/bin/bash
set -euo pipefail

PGUSER=postgres
PGDB=postgres

# Start background load in a separate process
PGAPPNAME=bg_load psql -U "$PGUSER" -d "$PGDB" -v ON_ERROR_STOP=1 <<'SQL' &
set statement_timeout to '300s';
do $$
begin
   for i in 1..1000000 loop
     create temp table tt1 (
       f0 bigserial primary key,
       f1 int,
       f2 int,
       f3 int,
       f4 int,
       f5 int,
       f6 int,
       f7 int,
       f8 int,
       f9 int,
       f10 int);
     CREATE INDEX ON tt1(f1);
     CREATE INDEX ON tt1(f2);
     CREATE INDEX ON tt1(f3);
     CREATE INDEX ON tt1(f4);
     CREATE INDEX ON tt1(f5);
     CREATE INDEX ON tt1(f6);
     CREATE INDEX ON tt1(f7);
     CREATE INDEX ON tt1(f8);
     CREATE INDEX ON tt1(f9);
     CREATE INDEX ON tt1(f10);
     drop table tt1;
     commit;
   end loop;
end;
$$;
SQL
BG_PID=$!

## Give the background load some time to kick in
sleep 2

#Run the main pgbench test
pgbench -U "$PGUSER" -d "$PGDB" -c 64 -j 32 -T 200 -s 100 -M prepared -b select-only -n &
PGBENCH_PID=$!

#Wait for pgbench finish
wait $PGBENCH_PID

# Kill psql
kill $BG_PID

#Terminate backend with background load
psql -U "$PGUSER" -d "$PGDB" -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE application_name='bg_load' AND state='active';"