Re: What is the most optimal config parameters to keep stable write TPS ?..

Hi Kevin,

PostgreSQL: 8.3.7 & 8.4
Server: Sun M5000 32cores
OS: Solaris 10

current postgresql.conf:

#================================
max_connections = 2000 # (change requires restart)
effective_cache_size = 48000MB
shared_buffers = 12000MB
temp_buffers = 200MB
work_mem = 100MB # min 64kB
maintenance_work_mem = 600MB # min 1MB

max_fsm_pages = 2048000
fsync = on # turns forced synchronization on or off
synchronous_commit = off # immediate fsync at commit
wal_sync_method = fdatasync
wal_buffers = 2MB
wal_writer_delay = 400ms # 1-10000 milliseconds

checkpoint_segments = 128
checkpoint_timeout = 30s

archive_mode = off
track_counts = on
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_max_workers = 4
autovacuum_naptime = 20 # time between autovacuum runs
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.001

lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'

#================================

Rgds,
-Dimitri

On 5/11/09, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Dimitri <dimitrik(dot)fr(at)gmail(dot)com> wrote:
>
>> what may you suggest as the most optimal postgresql.conf to keep
>> writing as stable as possible?..
>>
>> What I want is to avoid "throughput waves" - I want to keep my
>> response times stable without any activity holes. I've tried to
>> reduce checkpoint timeout from 5min to 30sec - it helped, throughput
>> is more stable now, but instead of big waves I have now short waves
>> anyway..
>>
>> What is the best options combination here?..
>
> What version of PostgreSQL? What operating system? What hardware?
>
> The answers are going to depend on the answers to those questions.
>
> It would also be good to show all lines from postgresql.conf which are
> not commented out.
>
> -Kevin
>