X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 79134563A7 for ; Wed, 9 Mar 2005 01:13:35 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 65770-07 for ; Wed, 9 Mar 2005 01:13:27 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.193]) by svr1.postgresql.org (Postfix) with ESMTP id 5AC36556EA for ; Wed, 9 Mar 2005 01:13:25 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 68so134705wra for ; Tue, 08 Mar 2005 17:13:29 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=T3FRMuLjsVwIb0EM0M7bVKPI+60RkTez36tYs0tKPZpTqVJzSujRBC0PlNHxi5P577mFrb4tj0274gk/YKLBiC+LTgTke5rg75Mj5HvPJZ6Ec8uxpVVpIg7tI8pMCICvLSx+1pxaQXBlQJ0ySSE4d4G3nVEXOtugo46SQSAOq+k= Received: by 10.54.45.41 with SMTP id s41mr338450wrs; Tue, 08 Mar 2005 17:13:29 -0800 (PST) Received: by 10.54.14.41 with HTTP; Tue, 8 Mar 2005 17:13:29 -0800 (PST) Message-ID: <19ab0ccd05030817132e702cd4@mail.gmail.com> Date: Tue, 8 Mar 2005 20:13:29 -0500 From: Aaron Birkland Reply-To: apb18@cornell.edu To: josh@agliodbs.com Subject: Re: Why would writes to pgsql_tmp bottleneck at 1mb/s? Cc: "pgsql-performance@postgresql.org" In-Reply-To: <200503081644.32497.josh@agliodbs.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <200503081218.24093.josh@agliodbs.com> <200503081617.57190.josh@agliodbs.com> <200503081644.32497.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.211 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200503/148 X-Sequence-Number: 10853 > Maybe I'm not an idiot (really!) even with almost 2GB of maintenance_mem, PG > still writes to pgsql_tmp no faster than 2MB/s. I think there may be an > artificial bottleneck there. Question is, PostgreSQL, OS or hardware? I'm curious: what is your cpu usage while this is happening? I've noticed similar slow index creation behaviour, but I did not make any connection to pgsql_temp (because it was not on a separate partition). I was indexing an oid field of a 700GB table and it took about four days on a 1.2GHz UltraSparcIII (solaris 9, 8GB core). I noticed that the one CPU that was pegged at near 100%, leading me to believe it was CPU bound. Odd thing is that the same operation on a 2GHz Pentium IV box (Linux) on the same data took about a day. Truss showed that a great majority of that time was in userland. -Aaron