perf-checksum.sh

#!/bin/bash -e

# perf_checksum.sh
#
# Test cases to show performance changes when database checksums
# are used
#
# Requires that PGDATA is set to the cluster location.
# ** This will erase everything in your PGDATA directory! **
#
# Jeff Davis <pgsql@j-davis.com> and Greg Smith <greg@2ndquadrant.com>
# Released under The PostgreSQL Licence

if [ -z "$PGDATA" ] ; then
  echo Cannot run without PGDATA set, yours is empty
  exit 1
fi

if [ ! -d $PGDATA ] ; then
  echo Cannot run without correctly set PGDATA
  exit 1
fi

PGLOG="$PGDATA/pg.log"

function stop {
  if [ -f "$PGDATA/postmaster.pid" ] ; then
    pg_ctl stop -D $PGDATA -m immediate
  else
    echo "Server doesn't appear started"
  fi
}

function start {
  if [ -f "$PGDATA/postmaster.pid" ] ; then
    echo "Server appears started:"
    ls -l $PGDATA/postmaster.pid
  else
    pg_ctl start -D $PGDATA -l $PGLOG -w
  fi
}

# Find worst-case overhead for the checksum calculation on write
function perf1 {

psql << COMMANDS
\timing
drop table if exists foo;
create table foo(i int, j int) with (fillfactor=50);
create index foo_idx on foo(i);
insert into foo select g % 25, -1 from generate_series(1,10000000) g;
checkpoint;
COMMANDS

psql -c "select pg_size_pretty(pg_relation_size('foo'));"
sleep 15
echo Executing OS sync
sync
sleep 15

# Ignore the update time here.  The amount of time the checkpoint
# takes is the important one.
psql << COMMANDS
\timing on
update foo set j=-1 where i = 0;
select pg_sleep(2);
checkpoint;

# Repeat the test two more times
update foo set j=-1 where i = 0;
select pg_sleep(2);
checkpoint;

update foo set j=-1 where i = 0;
select pg_sleep(2);
checkpoint;
COMMANDS
}

# Worst-case overhead for calculating checksum while reading data
function perf2 {

psql << COMMANDS
\timing
drop table if exists foo;
create table foo(i int, j int) with (fillfactor=50);
insert into foo select g % 25, -1 from generate_series(1,10000000) g;
-- Make sure hint bits and PD_ALL_VISIBLE are set everywhere
select count(*) from foo;
vacuum;
vacuum;
vacuum;
COMMANDS

psql -c "select pg_size_pretty(pg_relation_size('foo'))"

RELPATH=`psql -Atq -c "SELECT pg_relation_filepath(oid) FROM pg_class WHERE relname='foo'"`
stop
start

if [ ! -f $PGDATA/$RELPATH ] ; then
  echo Could not find relation expected at $PGDATA/RELPATH
  exit 1
fi

# Prime the OS cache with the table data
cat $PGDATA/$RELPATH > /dev/null
psql << COMMANDS
\timing on
SELECT COUNT(*) FROM foo;
COMMANDS

# Repeat the test two more times
stop
start
psql << COMMANDS
\timing on
SELECT COUNT(*) FROM foo;
COMMANDS

stop
start
psql << COMMANDS
\timing on
SELECT COUNT(*) FROM foo;
COMMANDS
}

function perf3 {
psql << COMMANDS
\timing
drop table if exists foo;
create table foo(i int, j int) with (fillfactor=50);
insert into foo select g % 25, -1 from generate_series(1,10000000) g;
checkpoint;
select pg_sleep(1);
checkpoint;
COMMANDS

psql -c "select pg_size_pretty(pg_relation_size('foo'));"
sleep 15
echo Executing OS sync
sync
sleep 15

START_WAL=`psql -Atq  -c "select pg_current_xlog_location()"`

psql << COMMANDS
\timing on
SELECT COUNT(*) FROM foo;
COMMANDS

END_WAL=`psql -Atq  -c "select pg_current_xlog_location()"`
psql -c "SELECT wal_lsn('$END_WAL') - wal_lsn('$START_WAL') AS wal_written"
}

# Test current behavior without checksums
function setup_standard {
stop
rm -rf $PGDATA/*
initdb 2>&1 > /dev/null
cat perf-checksum.conf >> $PGDATA/postgresql.conf
pg_controldata | grep checksum
start
createdb `whoami`
psql -f wal-functions.sql
}

# Create a checksummed database and run the same tests
function setup_checksum {
stop
rm -rf $PGDATA/*
initdb -k > /dev/null
cat perf-checksum.conf >> $PGDATA/postgresql.conf
start
pg_controldata | grep checksum
createdb `whoami`
psql -f wal-functions.sql
}

#
# Main test program
#

echo === Test 1:  Calc write overhead ===
exit 1
setup_standard
perf1
setup_checksum
perf1

echo === Test 2:  Reading data ===
setup_standard
perf2
setup_checksum
perf2

echo === Test 3:  WAL Overhead ===
setup_standard
perf3
perf3
perf3

setup_checksum
perf3
perf3
perf3