Re: Speed while runnning large transactions.

On Thu, 24 Sep 2009, jesper@krogh.cc wrote:

> I have a transaction running at the database for around 20 hours .. still
> isn't done. But during the last hours it has come to the point where it
> really hurts performance of "other queries".

Open transactions grab an internal resource named a snapshot that lets
them keep a consistent view of the database while running.  If the
transaction runs for a long time, that snapshot gets further and further
behind, and it takes increasingly long to do some operations as a result.
One common problem is that VACUUM can't do its normal cleanup for things
that happened since the long running transaction began.

I'm not aware of any good way to monitor or quanitify how bad snapshot
related debris is accumulating, that's actually something I'd like to add
more visibility to one day.  About all you can do is note the old
transaction in pg_stat_activity and presume it's potential impact
increases the longer the transaction is open.

There are only two good solutions here:

1) Rearchitect the app with the understanding that this problem exists and
there's no easy way around it, breaking commits into smaller pieces.

2) Test if an upgrade to PG 8.4 improves your situation.  There is some
new code in that version (labeled in the release notes as "Track
transaction snapshots more carefully") that has improved problems in this
area quite a bit for me.  There's a bit more detail about the change at
http://archives.postgresql.org/pgsql-committers/2008-05/msg00220.php , all
of the other descriptions I found of it require a lot of internals
knowledge to read.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD