Skip to content

Instantly share code, notes, and snippets.

@macdice

macdice/test-shared-buffer-sizes.sh

Created May 14, 2018 10:50
Show Gist options
  • Save macdice/b2ba8d8a447cba984968d12197fb2bea to your computer and use it in GitHub Desktop.
Save macdice/b2ba8d8a447cba984968d12197fb2bea to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. macdice created this gist May 14, 2018.
    37 changes: 37 additions & 0 deletions test-shared-buffer-sizes.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,37 @@
    #!/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