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 <k...@rice.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 >
