From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Ben <bench@silentmedia.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: big transaction slows down over time - but disk seems
Date: Wed, 01 Nov 2006 09:39:59 +0000
Message-ID: <45486B6F.2000704@enterprisedb.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0610312141430.5452@GRD.cube42.tai.silentmedia.com>
References: <Pine.LNX.4.64.0610312141430.5452@GRD.cube42.tai.silentmedia.com>
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
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: heikki@enterprisedb.com, bench@silentmedia.com
Subject: Re: big transaction slows down over time - but disk seems
In-Reply-To: <45486B6F.2000704@enterprisedb.com>
* 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