Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 64B1F9FB907 for ; Thu, 11 Oct 2007 11:27:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 29213-04 for ; Thu, 11 Oct 2007 11:27:17 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.175]) by postgresql.org (Postfix) with ESMTP id 969B69FB8F3 for ; Thu, 11 Oct 2007 11:27:25 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id j3so452696ugf for ; Thu, 11 Oct 2007 07:27:23 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=JY85H1b0FCezA1L5I5WUMQ3BkkX+xRuSRnBe+153/NE=; b=SrqeqViXD4e37dYbpuPm/Kh6PFZlCgjGKmmDiu69yXkFCQyNAhG7bqbIDs2VGdaDWpeEJVBBCXEWy8zUcBWgfNSBNN9iVkzqiTTKqG0S5tJlSzcAWiSRxHUAQKNq6ZnhcLbp5T5KODYp7qbKNNB72FVWwOjNyB7SYww+DRSGyHo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Zyz7OlMcuo8itjjhV+/0FV8olmvNFOtEuqzH3b8d/DyqXtPUG1H18EZPZZjbca0p/Nno2pOizCfTBw3cJAXnUfzVpbMwnmLMKp78QN1m8pqk7jFvQQo9ndGBeYHQ4ArT4+4YmhNJMC8HIq34dXZ0r/nnB+GJvVFklCBE924X8Oo= Received: by 10.67.86.19 with SMTP id o19mr3219982ugl.1192112843229; Thu, 11 Oct 2007 07:27:23 -0700 (PDT) Received: by 10.67.29.6 with HTTP; Thu, 11 Oct 2007 07:27:23 -0700 (PDT) Message-ID: Date: Thu, 11 Oct 2007 09:27:23 -0500 From: "Scott Marlowe" To: "henk de wit" Subject: Re: Huge amount of memory consumed during transaction Cc: "pgsql-performance@postgresql.org" In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/129 X-Sequence-Number: 27151 On 10/11/07, henk de wit wrote: > > Hi, > > I'm running into a problem with PostgreSQL 8.2.4 (running on 32 bit Debian > Etch/2x dual core C2D/8GB mem). The thing is that I have a huge transaction > that does 2 things: 1) delete about 300.000 rows from a table with about 15 > million rows and 2) do some (heavy) calculations and re-insert a litlte more > than 300.000 new rows. > > My problem is that this consumes huge amounts of memory. The transaction > runs for about 20 minutes and during that transaction memory usage peaks to > about 2GB. Over time, the more rows that are involved in this transaction, > the higher the peak memory requirements. How is the memory consumed? How are you measuring it? I assume you mean the postgres process that is running the query uses the memory. If so, which tool(s) are you using and what's the output that shows it being used? I believe that large transactions with foreign keys are known to cause this problem. > Lately we increased our shared_buffers to 1.5GB, and during this transaction > we reached the process memory limit, causing an out of memory and a rollback > of the transaction: How much memory does this machine have? You do realize that shared_buffers are not a generic postgresql memory pool, but explicitly used to hold data from the discs. If you need to sort and materialize data, that is done with memory allocated from the heap. If you've given all your memory to shared_buffers, there might not be any left. How much swap have you got configured? Lastly, what does explain say?