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

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