From: Alvaro Herrera <alvherre@commandprompt.com>
To: Michal Szymanski <szymanskim@gdfi.pl>
Cc: Michal Szymanski <szymanskim@datera.pl>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Big problem with sql update operation
Date: Tue, 29 May 2007 16:41:25 -0400
Message-ID: <20070529204124.GG11630@alvh.no-ip.org> (raw)
In-Reply-To: <4659BB70.7070200@gdfi.pl>
References: <4656A4E0.7020400@datera.pl>
<8751.1180103318@sss.pgh.pa.us>
<465762B6.3080608@datera.pl>
<20070525223828.GE24789@alvh.no-ip.org>
<4659BB70.7070200@gdfi.pl>
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)
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: alvherre@commandprompt.com, szymanskim@gdfi.pl, szymanskim@datera.pl, tgl@sss.pgh.pa.us
Subject: Re: Big problem with sql update operation
In-Reply-To: <20070529204124.GG11630@alvh.no-ip.org>
* 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