"John Smith" <sodgodofall@gmail.com> writes:
> The transaction where COMMIT PREPARED was slow basically did the
> following 20 times:
> - create a child table T1 as select from another child table T2
> - drops child table T2
> - renames T1 to T2
> What here would cause the 2PC state file to grow large? In my rough
> experiments, its size seems constant in the number of row locks held,
> and linear in the number of table locks held. Is there any state in
> that file that grows linearly with the size of the data touched in the
> transaction?
It was the number of locks I was speculating about. But that pattern
shouldn't result in more than 20 or so locks, so it's still not clear
what's happening.
In any case, a prepared xact is already holding all the locks it needs,
so COMMIT PREPARED shouldn't have to block on anything.
Have you tried watching the committing process with top, vmstat,
"select * from pg_stat_activity", etc? That should at least give you a
clue whether it's CPU-bound or IO-bound or (against the above theory)
blocked on something.
regards, tom lane