On Jul 28, 2009, at 1:16 PM, "Dan Boeriu" <dan(dot)boeriu(at)roost(dot)com> wrote:
> I am doing a reply-all - hope that's fine.
> The pg_locks shows only locks held by that Postgresql process - I am
> the only one on the box.
> pg_stat_activity shows 2 things:
> 1) the update
> 2) vacuum on the updated table
> Now, I tried to isolate the problem even more and turned the auto
> vacuum OFF.
> Then pg_stat_activity shows only the UPDATE statement.
> Here is one wierd thing:
> IF I cancel the second UPDATE then restart it then it runs in 20 secs.
> Let me repeat the problem: I run 2 SQL UPDATEs (the same statement)
> in parallel.
> The first one to acquire the locks on the updated table goes through
> in 20 secs.
> The second will not finish in 24h but the CPU stays at 100% and
> iostat shows no IO other than the checkpoints.
> IF I cancel the second UPDATE (pg_cancel_backend) and restart it
> (new pgsql invocation) ASAP it goes through in 20 secs.
> Dan Boeriu
> Senior Architect - Roost.com
> P: (415) 742 8056
> Roost.com - 2008 Inman Award Winner for Most Innovative New Technology
> -----Original Message-----
> From: Craig Ringer [mailto:craig(at)postnewspapers(dot)com(dot)au]
> Sent: Mon 7/27/2009 11:13 PM
> To: Dan Boeriu; PostgreSQL bugs
> Subject: RE: [BUGS] BUG #4945: Parallel update(s) gone wild
> Please reply to the list, not directly to me.
> > I don't think is that simple. The VERY SAME statement runs twice -
> > finishes in about 20 secs the other doesn't finish in 24 hours.
> Yep, OK, so it's not just a planning or scaling issue.
How about posting:
EXPLAIN ANALYZE output
Schemas of relevant tables
Or far better still:
A self-contained reproducible test case
In response to
pgsql-bugs by date
|Next:||From: Robert Haas||Date: 2009-07-29 02:05:04|
|Subject: Re: BUG #4949: NOT IN is prohibitive slower than the rewrite for medium to large sets|
|Previous:||From: Jim Michaels||Date: 2009-07-28 22:57:50|
|Subject: BUG #4951: installation dir wrong for libpq compilation|