From: Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Long Running Update
Date: Fri, 24 Jun 2011 13:39:16 +0100
Message-ID: <4E048574.6010706@riskcontrollimited.com> (raw)
In-Reply-To: <BANLkTi=bqcncLrpTcTOowj3=wkO1Zn4cew@mail.gmail.com>
References: <4E035649.20007@riskcontrollimited.com>
<BANLkTi=gt+LpTijBxtDp+k49fLFOFFZWQw@mail.gmail.com>
<4E0472AB.9090507@riskcontrollimited.com>
<BANLkTi=bqcncLrpTcTOowj3=wkO1Zn4cew@mail.gmail.com>
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".
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: harry.mantheakis@riskcontrollimited.com, klaussfreire@gmail.com
Subject: Re: Long Running Update
In-Reply-To: <4E048574.6010706@riskcontrollimited.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox