Re: big transaction slows down over time - but disk seems

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