From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephen Denne <Stephen.Denne@datamail.co.nz>
Cc: pgsql-performance@postgresql.org
Subject: Re: 8.3rc1 Out of memory when performing update
Date: Sun, 27 Jan 2008 23:16:20 -0500
Message-ID: <17436.1201493780@sss.pgh.pa.us> (raw)
In-Reply-To: <F0238EBA67824444BC1CB4700960CB480482B748@dmpeints002.isotach.com>
References: <F0238EBA67824444BC1CB4700960CB480482B525@dmpeints002.isotach.com>
<21675.1201228274@sss.pgh.pa.us>
<F0238EBA67824444BC1CB4700960CB480482B5B7@dmpeints002.isotach.com>
<22975.1201236625@sss.pgh.pa.us>
<F0238EBA67824444BC1CB4700960CB48036E81E2@dmpeints002.isotach.com>
<F0238EBA67824444BC1CB4700960CB480482B748@dmpeints002.isotach.com>
"Stephen Denne" <Stephen.Denne@datamail.co.nz> writes:
> So dropping the fk constraint and index results in successful query execution with constant memory usage. Does this confirm that the memory leak you found is the one I was suffering from?
Well, it confirms that you were suffering from that memory leak. What's
not clear is whether the leak completely explains the bad performance
you saw. The leak would have resulted in extra swapping, but I wouldn't
have thought it would drive the machine completely into swap hell.
Would the monitoring tools you were using have shown swapping I/O?
If you want to try a focused experiment, please apply the patch shown
here:
http://archives.postgresql.org/pgsql-committers/2008-01/msg00322.php
and see if the behavior gets better.
> I'd also class the extremely poor performance of the alternate query as a bug.
Yeah, we should look into that. The plan you showed before estimated
about 16.9M rows in "document" --- is that about right? What have you
got work_mem set to?
> Aside: I must say that I am impressed with PostgreSQL's handling of this connection. It recovers extremely well from running out of memory, cancelling very long running queries, reloading config (to turn autovacuum off), and continues to work as expected (the 3 day old connection that is).
Hey, we've had plenty of opportunity to improve the system's robustness
in the face of such things ;-)
regards, tom lane
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: tgl@sss.pgh.pa.us, Stephen.Denne@datamail.co.nz
Subject: Re: 8.3rc1 Out of memory when performing update
In-Reply-To: <17436.1201493780@sss.pgh.pa.us>
* 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