Re: pgtune + configurations with 9.3

Another apology:

My pg_version is 9.3
and here are more up to date png's.

On 26 November 2014 at 15:34, Johann Spies <johann(dot)spies(at)gmail(dot)com> wrote:

> 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)
>

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