Re: Comparitive UPDATE speed

On Fri, Oct 04, 2002 at 12:09:42PM -0700, Josh Berkus wrote:

> BTW, in case you missed it, the real concern is that an UPDATE query similar 
> to the SELECT query we are discussing takes over 10 minutes, which on this 
> hardware is ridiculous.  Robert suggested that we test the SELECT query to 
> see if there were general performance problems; apparently, there are.

Yes, that's my thought, too.

> Postgresql.conf has: fdatasync, various chared memory tuned to allocate 256mb 
> to postgres (which seems to be working correctly).

Hmm.  Are you swapping?  Lots of temp files?  (I presume you've been
over all that.)  Half your physical memory seems pretty dangerous to
me.  If oyu reduce that, does it help?

> When the UPDATE query takes a long time, I generally can watch the log hover 
> in the land of "Reaping dead child processes" for 30-90 seconds per 
> iteration.

Ick.  Hmm.  What sort of numbers do you get from vmstat, iostat, sar,
and friends?

A
-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110