Thanks for the quick response, Andrew. How could the 'commit prepared'
be I/O bound? Isn't all the I/O accomplished during 'prepare
transaction', leaving only a tiny bit of data to write at commit?
>
> You could be I/O bound. Have a look at iostat and sar.
>
> A
> --
> Andrew Sullivan
> ajs(at)crankycanuck(dot)ca
>
>
>
> On Tue, Nov 25, 2008 at 7:17 PM, John Smith <sodgodofall@gmail.com> 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.
> >
> > Thanks for your help,
> > John
> >
>