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

Hi Scott,

good point - the current checkpoint completion target is a default
0.5, and it makes sense to set it to 0.8 to make writing more smooth,
great!

yes, data and xlog are separated, each one is sitting on an
independent storage LUN RAID1, and storage box is enough performant

Thank you!

Rgds,
-Dimitri


On 5/11/09, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Mon, May 11, 2009 at 10:31 AM, Dimitri <dimitrik.fr@gmail.com> wrote:
>> 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
>
> What's your checkpoint completion target set to? Crank that up a bit
> ot 0.7, 0.8 etc and make the timeout more, not less.  That should
> help.
>
> Also, look into better hardware (RAID controller with battery backed
> cache) and also putting pg_xlog on a separate RAID-1 set (or RAID-10
> set if you've got a lot of drives under the postgres data set).
>