Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id A3D099FB422 for ; Tue, 16 Oct 2007 12:39:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 91175-01 for ; Tue, 16 Oct 2007 12:38:55 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id B023B9FB325 for ; Tue, 16 Oct 2007 12:39:01 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l9GFcuHJ017245; Tue, 16 Oct 2007 11:38:57 -0400 (EDT) To: Richard Huxton cc: Jason Lustig , pgsql-performance@postgresql.org Subject: Re: Autovacuum running out of memory In-reply-to: <16500.1192547417@sss.pgh.pa.us> References: <374EB3D1-EE74-4B8C-9EEC-104ADD097568@brandeis.edu> <4714A458.5060408@archonet.com> <4714AD30.6000103@archonet.com> <16500.1192547417@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Tue, 16 Oct 2007 11:10:17 -0400" Date: Tue, 16 Oct 2007 11:38:56 -0400 Message-ID: <17244.1192549136@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/188 X-Sequence-Number: 27210 I wrote: > ... The weird thing about this > is why the large maintenance_work_mem works for a regular session and > not for autovacuum. There really shouldn't be much difference in the > maximum workable setting for the two cases, AFAICS. After re-reading the thread I realized that the OP is comparing manual VACUUM FULL to automatic plain VACUUM, so the mystery is solved. Plain VACUUM tries to grab a maintenance_work_mem-sized array of tuple IDs immediately at command startup. VACUUM FULL doesn't work like that. Given the 200M ulimit -v, and the shared_buffers setting of 20000 (about 160M), the behavior is all explained if we assume that shared memory counts against -v. Which I think it does. regards, tom lane