Re: Performance issues - Mailing list pgsql-performance

On Mon, Mar 07, 2011 at 02:45:03PM +0100, Andreas For? Tollefsen wrote:
> Hi,
>
> I am running Postgresql 8.4.7 with Postgis 2.0 (for raster support).
> Server is mainly 1 user for spatial data processing. This involves queries
> that can take hours.
>
> This is running on a ubuntu 10.10 Server with Core2Duo 6600 @ 2.4 GHZ, 6 GB
> RAM.
>
> My postgresql.conf:
> # - Memory -
> shared_buffers = 1024MB                 # min 128kB
>                                         # (change requires restart)
> temp_buffers = 256MB                    # min 800kB
> #max_prepared_transactions = 0          # zero disables the feature
>                                         # (change requires restart)
> # Note:  Increasing max_prepared_transactions costs ~600 bytes of shared
> memory
> # per transaction slot, plus lock space (see max_locks_per_transaction).
> # It is not advisable to set max_prepared_transactions nonzero unless you
> # actively intend to use prepared transactions.
> work_mem = 1024MB                               # min 64kB
> maintenance_work_mem = 256MB            # min 1MB
> max_stack_depth = 7MB                   # min 100kB
> wal_buffers = 8MB
> effective_cache_size = 3072MB
>
> Everything else is default.
>
> My Pgbench results:
> /usr/lib/postgresql/8.4/bin/pgbench -T 60 test1
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 1
> query mode: simple
> number of clients: 1
> duration: 60 s
> number of transactions actually processed: 7004
> tps = 116.728199 (including connections establishing)
> tps = 116.733012 (excluding connections establishing)
>
>
> My question is if these are acceptable results, or if someone can recommend
> settings which will improve my servers performance.
>
> Andreas

Your results are I/O limited. Depending upon your requirements,
you may be able to turn off synchronous_commit which can help.
Your actual workload may be able to use batching to help as well.
Your work_mem looks pretty darn high for a 6GB system.

Cheers,
Ken