Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id E95FF9FB5F0 for ; Wed, 1 Nov 2006 05:42:23 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 01865-03 for ; Wed, 1 Nov 2006 09:40:04 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.169]) by postgresql.org (Postfix) with ESMTP id 492DC9FB306 for ; Wed, 1 Nov 2006 05:40:04 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id 30so1462905ugc for ; Wed, 01 Nov 2006 01:40:03 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; b=SrhQvdzP1WfofSXQNrXlMoAO+mGMulUcrwWJwsn+F+nVmRwU1jJmfiow5/5tOwsk5lKrAuBy/ByOotY7OSEkuBuLTBTct7E5oVAaJ0KQ6Yg7uNZg75bVKwJ0mm3H04hO0H0kbhFuNeHlpuHZUKlsCaq8O6RGTg+lXK1YQuVTHM0= Received: by 10.67.119.9 with SMTP id w9mr7836600ugm; Wed, 01 Nov 2006 01:40:02 -0800 (PST) Received: from ?192.168.0.22? ( [62.232.55.118]) by mx.google.com with ESMTP id j1sm386185ugf.2006.11.01.01.40.00; Wed, 01 Nov 2006 01:40:01 -0800 (PST) Message-ID: <45486B6F.2000704@enterprisedb.com> Date: Wed, 01 Nov 2006 09:39:59 +0000 From: Heikki Linnakangas User-Agent: Thunderbird 1.5.0.7 (X11/20060927) MIME-Version: 1.0 To: Ben CC: pgsql-performance@postgresql.org Subject: Re: big transaction slows down over time - but disk seems References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/7 X-Sequence-Number: 21777 Ben wrote: > I've got a long-running, update-heavy transaction that increasingly > slows down the longer it runs. I would expect that behavior, if there > was some temp file creation going on. But monitoring vmstat over the > life of the transaction shows virtually zero disk activity. Instead, the > system has its CPU pegged the whole time. > > So.... why the slowdown? Is it a MVCC thing? A side effect of calling > stored proceedures a couple hundred thousand times in a single > transaction? Or am I just doing something wrong? My guess is that the updates are creating a lot of old row versions, and a command within the transaction is doing a seq scan that has to scan through all of them. Or something like that. It's hard to tell without more details. Calling stored procedures repeatedly shouldn't cause a slowdown over time. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com