Re: Performance issues

Thanks, Ken.

It seems like the tip to turn off synchronous_commit did the trick:

/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: 86048
tps = 1434.123199 (including connections establishing)
tps = 1434.183362 (excluding connections establishing)

Is this acceptable compared to others when considering my setup?

Cheers,
Andreas

2011/3/7 Kenneth Marshall <ktm(at)rice(dot)edu>

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