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

Re: Big problem with sql update operation

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Michal Szymanski <szymanskim(at)gdfi(dot)pl>
Cc: Michal Szymanski <szymanskim(at)datera(dot)pl>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Big problem with sql update operation
Date: 2007-05-29 20:41:25
Message-ID: 20070529204124.GG11630@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-performance
Michal Szymanski wrote:
> There is another strange thing. We have two versions of our test
> >>environment one with production DB copy and second genereated with 
> >>minimal data set and it is odd that update presented above on copy of 
> >>production is executing 170ms but on small DB it executing 6s !!!!
> >
> >How are you vacuuming the tables?
> >  
> Using pgAdmin (DB is installed on my laptop) and I use this tool for 
> vaccuminh, I do not think that vaccuming can help because I've tested on 
> both database just after importing.

I think you are misunderstanding the importance of vacuuming the table.
Try this: on a different terminal from the one running the test, run a
VACUUM on the updated table with vacuum_cost_delay set to 20, on an
infinite loop.  Keep this running while you do your update test.  Vary
the vacuum_cost_delay and measure the average/min/max UPDATE times.
Also try putting a short sleep on the infinite VACUUM loop and see how
its length affects the UPDATE times.

One thing not clear to me is if your table is in a clean state.  Before
running this test, do a TRUNCATE and import the data again.  This will
get rid of any dead space that may be hurting your measurements.

-- 
Alvaro Herrera                        http://www.advogato.org/person/alvherre
"The Postgresql hackers have what I call a "NASA space shot" mentality.
 Quite refreshing in a world of "weekend drag racer" developers."
(Scott Marlowe)

In response to

pgsql-performance by date

Next:From: Dave PageDate: 2007-05-29 20:43:46
Subject: Re: Vacuum takes forever
Previous:From: Joost KraaijeveldDate: 2007-05-29 17:56:07
Subject: Re: Vacuum takes forever

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