Skip site navigation (1) Skip section navigation (2)

Re: slow, long-running 'commit prepared'

From: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: slow, long-running 'commit prepared'
Date: 2008-11-26 03:32:23
Message-ID: 20081126033223.GB11225@shinkuro.com (view raw or flat)
Thread:
Lists: pgsql-general
On Tue, Nov 25, 2008 at 07:17:20PM -0800, John Smith wrote:
> I have a pg instance with 700GB of data, almost all of which is in one
> table. When I PREPARE and then COMMIT PREPARED a transaction that
> reads & writes to a large fraction of that data (about 10%,
> effectively randomly chosen rows and so every file in the table is
> modified), the COMMIT PREPARED sometimes takes a very long time--2 to
> 5 minutes. Is this expected? Is it possible for the commit to hang
> waiting on some lock for this long? I haven't yet been able to examine
> pg_locks during this 5 minute delay. There is very little
> concurrency--only a couple of open sessions--when the COMMIT PREPARED
> is issued.

You could be I/O bound.  Have a look at iostat and sar.

A
-- 
Andrew Sullivan
ajs(at)crankycanuck(dot)ca

In response to

pgsql-general by date

Next:From: Clemens SchwaighoferDate: 2008-11-26 03:39:14
Subject: Postgres 8.3 only uses seq scan
Previous:From: John SmithDate: 2008-11-26 03:17:20
Subject: slow, long-running 'commit prepared'

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group