Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id AFC05133616B for ; Mon, 7 Mar 2011 10:01:13 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 18189-05 for ; Mon, 7 Mar 2011 14:01:04 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mh7.mail.rice.edu (mh7.mail.rice.edu [128.42.199.46]) by mail.postgresql.org (Postfix) with ESMTP id F2D3D1337B8C for ; Mon, 7 Mar 2011 10:01:02 -0400 (AST) Received: from mh7.mail.rice.edu (localhost.localdomain [127.0.0.1]) by mh7.mail.rice.edu (Postfix) with ESMTP id 4EDC628FC4B; Mon, 7 Mar 2011 08:01:02 -0600 (CST) X-Virus-Scanned: by amavis-2.6.4 at mh7.mail.rice.edu, auth channel Received: from mh7.mail.rice.edu ([127.0.0.1]) by mh7.mail.rice.edu (mh7.mail.rice.edu [127.0.0.1]) (amavis, port 10026) with ESMTP id SCzDmGSkJ+dz; Mon, 7 Mar 2011 08:01:02 -0600 (CST) X-SMTP-Auth: no X-SMTP-Auth: no Received: from aart.is.rice.edu (aart.is.rice.edu [168.7.56.48]) by mh7.mail.rice.edu (Postfix) with ESMTP id 2391828FC47; Mon, 7 Mar 2011 08:01:02 -0600 (CST) Received: by aart.is.rice.edu (Postfix, from userid 18612) id E49AC2D5A6; Mon, 7 Mar 2011 08:01:01 -0600 (CST) Date: Mon, 7 Mar 2011 08:01:01 -0600 From: Kenneth Marshall To: Andreas For? Tollefsen Cc: pgsql-performance@postgresql.org Subject: Re: Performance issues Message-ID: <20110307140101.GL8169@aart.is.rice.edu> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.16 (2007-06-09) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/66 X-Sequence-Number: 42745 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