Re: pgtune + configurations with 9.3

Hello Greame,

It's probably helpful if everyone sharing this information can post their
> measurement process / settings and the results as completely as possible,
> for comparison and reference.
>

Apologies. I have only changed one parameter in postgresql.conf for the
tests and that was shared_buffers:

shared_buffers = 32GB # min 128k
shared_preload_libraries = 'auto_explain' # (change requires
restart)
vacuum_cost_delay = 5 # 0-100 milliseconds
wal_sync_method = open_sync # the default is the first option
wal_buffers = -1 # min 32kB, -1 sets based
on shared_buffers
checkpoint_completion_target = 0.9 # checkpoint target
duration, 0.0 - 1.0
checkpoint_warning = 30s # 0 disables
default_statistics_target = 100 # range 1-10000
log_line_prefix = '%t ' # special values:
log_statement = 'all' # none, ddl, mod, all
log_timezone = 'localtime'
autovacuum_vacuum_scale_factor = 0.1 # fraction of table size
before vacuum
autovacuum_vacuum_cost_delay = 5ms # default vacuum cost delay
for
datestyle = 'iso, dmy'
timezone = 'localtime'
lc_messages = 'en_ZA.UTF-8' # locale for system
error message
lc_monetary = 'en_ZA.UTF-8' # locale for
monetary formatting
lc_numeric = 'en_ZA.UTF-8' # locale for number
formatting
lc_time = 'en_ZA.UTF-8' # locale for time
formatting
default_text_search_config = 'pg_catalog.english'
auto_explain.log_min_duration = '6s' # Gregory Smith page 180
effective_cache_size = 512GB # pgtune wizard 2014-09-25
work_mem = 4608MB # pgtune wizard 2014-09-25
checkpoint_segments = 16 # pgtune wizard 2014-09-25
max_connections = 80 # pgtune wizard 2014-09-25

And pgbench-tools - the default configuration:

BASEDIR=`pwd`
PGBENCHBIN=`which pgbench`
TESTDIR="tests"
SKIPINIT=0
TABBED=0
OSDATA=1
TESTHOST=localhost
TESTUSER=`whoami`
TESTPORT=5432
TESTDB=pgbench
RESULTHOST="$TESTHOST"
RESULTUSER="$TESTUSER"
RESULTPORT="$TESTPORT"
RESULTDB=results
MAX_WORKERS=""
SCRIPT="select.sql"
SCALES="1 10 100 1000"
SETCLIENTS="1 2 4 8 16 32"
SETTIMES=3
RUNTIME=60
TOTTRANS=""
SETRATES=""

The server:

# See Gregory Smith: High Performans Postgresql 9.0 pages 81,82 for the
next lines
vm.swappiness=0
vm.overcommit_memory=2
vm.dirty_ratio = 2
vm.dirty_background_ratio=1
# Maximum shared segment size in bytes
kernel.shmmax = 406622322688
# Maximum number of shared memory segments in pages
kernel.shmall = 99273028

$ free
total used free shared buffers cached
Mem: 794184164 792406416 1777748 0 123676 788079892
-/+ buffers/cache: 4202848 789981316
Swap: 7906300 0 7906300

I have attached the resulting graphs.

Regards
Johann

--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)