From: Kenneth Marshall <ktm@rice.edu>
To: Andreas For? Tollefsen <andreasft@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance issues
Date: Mon, 7 Mar 2011 08:01:01 -0600
Message-ID: <20110307140101.GL8169@aart.is.rice.edu> (raw)
In-Reply-To: <AANLkTinZkVapdjrZULpc8zmwb4w8wotkYzj0gqj_jPXb@mail.gmail.com>
References: <AANLkTinZkVapdjrZULpc8zmwb4w8wotkYzj0gqj_jPXb@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ktm@rice.edu, andreasft@gmail.com
Subject: Re: Performance issues
In-Reply-To: <20110307140101.GL8169@aart.is.rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox