test-shared-buffer-sizes.sh

#!/bin/sh

# pgbench -i -s 90
# psql postgres -c "create extension pg_prewarm"

PATH=~/install/bin:$PATH
export PATH
PGDATA=~/pgdata
CONF=$PGDATA/postgresql.conf
SECONDS=60
OUTPUT=output.log

echo -n > $OUTPUT

for i in ` seq 1 19 ` ; do
  echo "==== test run for shared_buffers = ${i}00MB ===="
  grep -v -E "(shared_buffers|exclusive_caching)" < $CONF > $CONF.tmp
  mv $CONF.tmp $CONF
  echo "shared_buffers = '${i}00MB'" >> $CONF
  pg_ctl start -D $PGDATA
  psql postgres -c "create extension pg_buffercache"
  psql postgres -c "select pg_prewarm('pgbench_history', 'buffer', 'main')"
  psql postgres -c "select pg_prewarm('pgbench_accounts', 'buffer', 'main')"
  psql postgres -c "select pg_prewarm('pgbench_branches', 'buffer', 'main')"
  psql postgres -c "select pg_prewarm('pgbench_tellers', 'buffer', 'main')"
  psql postgres -c "select pg_prewarm('pgbench_accounts_pkey', 'buffer', 'main')"
  psql postgres -c "select pg_prewarm('pgbench_branches_pkey', 'buffer', 'main')"
  psql postgres -c "select pg_prewarm('pgbench_tellers_pkey', 'buffer', 'main')"
  tps=` pgbench -T $SECONDS -M prepared -S postgres | tail -1 | cut -d' ' -f3 `
  buffers=` psql postgres -t -A -c "create temp table kernelcache (reldatabase oid, relfilenode oid, relblocknumber int, pages int);
                                    copy kernelcache from program 'pgdata_mincore $PGDATA';
                                    select (select (count(*) * 8192) / (1024 * 1024) from pg_buffercache where relfilenode is not null) as pg_buffer,
                                           (select (count(*) * 8192) / (1024 * 1024) from kernelcache) as os_buffer,
                                           (select (count(*) * 8192) / (1024 * 1024) from kernelcache join pg_buffercache using (reldatabase, relfilenode, relblocknumber)) as double_buffer" | tr "|" " " `
  echo "${i}00 ${tps} ${buffers}" >> $OUTPUT
  pg_ctl stop -D $PGDATA
done