X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 9AD189FA6B0 for ; Fri, 9 Jun 2006 13:00:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 55822-02 for ; Fri, 9 Jun 2006 12:59:58 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 1E0FF9FA646 for ; Fri, 9 Jun 2006 12:59:57 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id B76E35647E; Fri, 9 Jun 2006 10:59:54 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Fri, 9 Jun 2006 10:59:53 -0500 Date: Fri, 9 Jun 2006 10:59:53 -0500 From: "Jim C. Nasby" To: "Domenico - Sal. F.lli Riva" Cc: pgsql-performance@postgresql.org Subject: Re: pgsql_tmp and postgres settings Message-ID: <20060609155953.GX45331@pervasive.com> References: <20060609122948.6B7419FA621@postgresql.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20060609122948.6B7419FA621@postgresql.org> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060609:domenico.mozzanica@fratelliriva.it::elY1cuFl6PGndMz7:000 0000000000000000000000002X5A X-Hashcash: 1:20:060609:pgsql-performance@postgresql.org::H9OVxGdTcjGib07P:00000 0000000000000000000000007lkh X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/112 X-Sequence-Number: 19469 On Fri, Jun 09, 2006 at 02:23:04PM +0200, Domenico - Sal. F.lli Riva wrote: > Hello, > > During insert or update, potgresql write in pgsql_tmp directory and so > performance are very poor. pgsql_tmp is used if a query runs out of work_mem, so you can try increasing that. > My configuration is: > > Work mem 10240 > > Effective_cache_size 30000 You're off by a factor of 10. > Shared buffers 9000 I'd suggest bumping that up to at least 30000. > Postgresql (RPM from official website) 8.1.0 You should upgrade to 8.1.4. There's a number of data loss bugs waiting to bite you. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461