Re: Long Running Update

On Fri, Jun 24, 2011 at 1:19 PM, Harry Mantheakis
<harry(dot)mantheakis(at)riskcontrollimited(dot)com> wrote:
>
>> there are lots of ways in which it could be struggling...
>
> I have been monitoring the server with IOSTAT -d and IOSTAT -c and I cannot
> see anything alarming.

If iostat doesn't show disk load, either iostat doesn't work well
(which could be the case, I've found a few broken installations here
and there), or, perhaps, your update is waiting on some other update.

I've seen cases when there are application-level deadlocks (ie,
deadlocks, but that the database alone cannot detect, and then your
queries stall like that. It happens quite frequently if you try such a
massive update on a loaded production server. In those cases, the
technique someone mentioned (updating in smaller batches) usually
works nicely.

You should be able to see if it's locked waiting for something with
"select * from pg_stat_activity".