Skip site navigation (1) Skip section navigation (2)

Re: BUG #4945: Parallel update(s) gone wild

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Dan Boeriu <dan(dot)boeriu(at)roost(dot)com>
Cc: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>, PostgreSQL bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #4945: Parallel update(s) gone wild
Date: 2009-07-29 01:20:11
Message-ID: 8F25CA32-0806-4681-8917-819EE75E98BC@gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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 -  
> one
> > 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:

Server version
EXPLAIN ANALYZE output
Schemas of relevant tables

Or far better still:

A self-contained reproducible test case

...Robert

In response to

Responses

pgsql-bugs by date

Next:From: Robert HaasDate: 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 MichaelsDate: 2009-07-28 22:57:50
Subject: BUG #4951: installation dir wrong for libpq compilation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group