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)