Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 0DD0BB5DBD2 for ; Sat, 9 Jul 2011 05:33:10 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 45793-04 for ; Sat, 9 Jul 2011 08:33:03 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sd-17937.dedibox.fr (sd-17937.dedibox.fr [88.191.100.74]) by mail.postgresql.org (Postfix) with ESMTP id C2E95B5DBC6 for ; Sat, 9 Jul 2011 05:33:03 -0300 (ADT) Received: from [192.168.10.3] (ASte-Genev-Bois-153-1-21-176.w83-112.abo.wanadoo.fr [83.112.201.176]) by sd-17937.dedibox.fr (Postfix) with ESMTPA id 48F1E3B8F5; Sat, 9 Jul 2011 10:33:03 +0200 (CEST) Subject: Re: Memory usage of auto-vacuum From: Guillaume Lelarge To: Gael Le Mignot Cc: pgsql-performance@postgresql.org Date: Sat, 09 Jul 2011 10:33:03 +0200 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" X-Mailer: Evolution 3.0.2 (3.0.2-3.fc15) Content-Transfer-Encoding: 7bit Message-ID: <1310200383.2101.8.camel@laptop> Mime-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201107/55 X-Sequence-Number: 44267 Hi, On Sat, 2011-07-09 at 09:25 +0200, Gael Le Mignot wrote: > [...] > We are running a PostgreSQL 8.4 database, with two tables containing a > lot (> 1 million) moderatly small rows. It contains some btree indexes, > and one of the two tables contains a gin full-text index. > > We noticed that the autovacuum process tend to use a lot of memory, > bumping the postgres process near 1Gb while it's running. > Well, it could be its own memory (see maintenance_work_mem), or shared memory. So, it's hard to say if it's really an issue or not. BTW, how much memory do you have on this server? what values are used for shared_buffers and maintenance_work_mem? > I looked in the documentations, but I didn't find the information : do > you know how to estimate the memory required for the autovacuum if we > increase the number of rows ? Is it linear ? Logarithmic ? > It should use up to maintenance_work_mem. Depends on how much memory you set on this parameter. > Also, is there a way to reduce that memory usage ? Reduce maintenance_work_mem. Of course, if you do that, VACUUM could take a lot longer to execute. > Would running the > autovacuum more frequently lower its memory usage ? > Yes. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com