dirtypages.sh

application/x-sh

Download

#!/bin/bash

NTABLES=2
TOTAL_SIZE=$((2000*1024*1024))   # in bytes, adjust to match size of shared_buffers

# Create a function to initialize the test tables.
psql <<EOF
CREATE OR REPLACE FUNCTION public.init_tables(ntables integer, total_size bigint)
 RETURNS void
 LANGUAGE plpgsql
AS \$function\$
declare
  n int;
  size bigint;
begin
  for n in 1..ntables loop
    execute 'drop table if exists foo' || n;
    execute 'CREATE TABLE foo' || n || ' (id int4) WITH (autovacuum_enabled=false, fillfactor=10);';
    size := 0;
    while size < total_size / ntables loop
      execute 'INSERT INTO foo' || n || ' SELECT a FROM generate_series(1, 1000) a';
      execute 'SELECT pg_relation_size(''foo' || n || ''')' into size;
    end loop;
    raise notice 'created table % / %', n, ntables;
  end loop;
end;
\$function\$;
EOF

psql -c "SELECT init_tables($NTABLES, $TOTAL_SIZE::bigint)"

# Clean all the pages before starting the test
psql -c "checkpoint;"

# Verify that the buffer cache is in desired state. Almost all pages should
# be clean at this point
psql -c "SELECT COUNT(*), isdirty FROM pg_buffercache GROUP BY isdirty;"
echo "starting test"
date
for ((i=1; i <= $NTABLES; i++))
do
    # A SELECT is enough to mark the pages dirty, because this sets hint bits
    psql -c "SELECT COUNT(*) FROM foo$i" &
done
wait
date
echo "done!"
psql -c "SELECT COUNT(*), isdirty FROM pg_buffercache GROUP BY isdirty;"
# Verify that the buffer cache is in desired state after test. Almost all
# pages should now be dirty