From owner-freebsd-stable@FreeBSD.ORG Wed Jun 29 23:12:20 2005 Return-Path: X-Original-To: stable@freebsd.org Delivered-To: freebsd-stable@FreeBSD.ORG Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id C389416A41C for ; Wed, 29 Jun 2005 23:12:20 +0000 (GMT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mx1.FreeBSD.org (Postfix) with ESMTP id 7A92443D49 for ; Wed, 29 Jun 2005 23:12:20 +0000 (GMT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j5TNCJIG000166; Wed, 29 Jun 2005 19:12:19 -0400 (EDT) To: Sven Willenberger In-reply-to: <1120084441.19614.87.camel@lanshark.dmv.com> References: <1120050088.19603.7.camel@lanshark.dmv.com> <58067976-00C6-4380-90DF-F448D9008C81@khera.org> <1120075946.19603.68.camel@lanshark.dmv.com> <51C9ABA2-20B8-4DE8-B647-A26A168A78FC@mac.com> <1120078715.19598.79.camel@lanshark.dmv.com> <1120084441.19614.87.camel@lanshark.dmv.com> Comments: In-reply-to Sven Willenberger message dated "Wed, 29 Jun 2005 18:34:01 -0400" Date: Wed, 29 Jun 2005 19:12:19 -0400 Message-ID: <165.1120086739@sss.pgh.pa.us> From: Tom Lane Cc: Vivek Khera , stable@freebsd.org, postgres general Subject: Re: [GENERAL] PostgreSQL's vacuumdb fails to allocate memory for X-BeenThere: freebsd-stable@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Production branch of FreeBSD source code List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 29 Jun 2005 23:12:20 -0000 Sven Willenberger writes: > I have found the answer/problem. On a hunch I increased maxdsiz to 1.5G > in the loader.conf file and rebooted. I ran vacuumdb and watched top as > the process proceeded. What I saw was SIZE sitting at 603MB (which was > 512MB plus another 91MB which corresponded nicely to the value of RES > for the process. A bit into the process I saw SIZE jump to 1115 -- i.e. > another 512 MB of RAM was requested and this time allocated. At one > point SIZE dropped back to 603 and then back up to 1115. I suspect the > same type of issue was occuring in regular vacuum from the psql client > connecting to the backend, for some reason not as frequently. I am > gathering that maintenance work mem is either not being recognized as > having already been allocated and another malloc is made or the process > is thinking the memory was released and tried to grab a chunk of memory > again. Hmm. It's probably a fragmentation issue. VACUUM will allocate a maintenance work mem-sized chunk during command startup, but that's likely not all that gets allocated, and if any stuff allocated after it is not freed at the same time, the process size won't go back down. Which wouldn't be a killer in itself, but unless the next iteration is able to fit that array in the same space, you'd see the above behavior. BTW, do you have any evidence that it's actually useful to set maintenance work mem that high for VACUUM? A quick and dirty solution would be to bound the dead-tuples array size at something more sane... regards, tom lane