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

Re: Out of Memory and Configuration Problems (Big Computer)

From: Bill Moran <wmoran(at)potentialtech(dot)com>
To: Tom Wilcox <hungrytom(at)googlemail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Out of Memory and Configuration Problems (Big Computer)
Date: 2010-05-28 16:54:49
Message-ID: 20100528125449.4190f7f0.wmoran@potentialtech.com (view raw or flat)
Thread:
Lists: pgsql-general
In response to Tom Wilcox <hungrytom(at)googlemail(dot)com>:

> In addition, I have discovered that the update query that runs on each row
> of a 27million row table and fails with Out of memory error will work when
> limited to 1million rows in an extremely shorter period of time:
> 
> EXPLAIN ANALYZE
>         UPDATE nlpg.match_data SET org = normalise(org) WHERE match_data_id
> < 1000000;
> 
> "Index Scan using match_data_pkey1 on match_data  (cost=0.00..3285969.97
> rows=1147685 width=206) (actual time=0.280..18018.601 rows=999999 loops=1)"
> "  Index Cond: (match_data_id < 1000000)"
> "Total runtime: 182732.207 ms"
> 
> 
> Whereas this fails with Out of Memory:
> 
> UPDATE nlpg.match_data SET org = normalise(org) WHERE match_data_id;

You're not liable to get shit for answers if you omit the mailing list from
the conversation, especially since I know almost nothing about tuning
PostgreSQL installed on Windows.

Are there multiple queries having this problem?  The original query didn't
have normalise() in it, and I would be highly suspicious that a custom
function may have a memory leak or other memory-intensive side-effects.
What is the code for that function?

For example, does:
UPDATE nlpg.match_data SET org = org WHERE match_data_id;
finish in a reasonable amount of time or exhibit the same out of memory
problem?

It'd be nice to see a \d on that table ... does it have any triggers or
cascading foreign keys?

And stop 

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

In response to

Responses

pgsql-general by date

Next:From: Nilesh GovindarajanDate: 2010-05-28 16:56:58
Subject: Re: No lidbl.so in libpq.so (postgresql 8.4.4)
Previous:From: Gary FuDate: 2010-05-28 16:43:59
Subject: Re: No lidbl.so in libpq.so (postgresql 8.4.4)

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