Message-ID: <4E048574.6010706@riskcontrollimited.com>
Date: Fri, 24 Jun 2011 13:39:16 +0100
From: Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-GB;
	rv:1.9.2.18) Gecko/20110616 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
To: Claudio Freire <klaussfreire@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Long Running Update
References: <4E035649.20007@riskcontrollimited.com>
	<BANLkTi=gt+LpTijBxtDp+k49fLFOFFZWQw@mail.gmail.com>
	<4E0472AB.9090507@riskcontrollimited.com>
	<BANLkTi=bqcncLrpTcTOowj3=wkO1Zn4cew@mail.gmail.com>
In-Reply-To: <BANLkTi=bqcncLrpTcTOowj3=wkO1Zn4cew@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Thanks again Claudio.

It does not look like its locked/blocked - but your hint about doing 
this task in smaller batches is a good one, and it would be easy enough 
to organise.

I am going to let this task run over the week-end, and then decide. 
Either way, I shall update this thread.

Much obliged!

Harry Mantheakis
London, UK


On 24/06/2011 12:39, Claudio Freire wrote:
> On Fri, Jun 24, 2011 at 1:19 PM, Harry Mantheakis
> <harry.mantheakis@riskcontrollimited.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".