Subject: Re: Memory usage of auto-vacuum
From: Guillaume Lelarge <guillaume@lelarge.info>
To: Gael Le Mignot <gael@pilotsystems.net>
Cc: pgsql-performance@postgresql.org
Date: Sat, 09 Jul 2011 10:53:14 +0200
In-Reply-To: <plop87liw7n8t0.fsf@aoskar.kilobug.org>
References: <plop87tyavncer.fsf@aoskar.kilobug.org>
	<1310200383.2101.8.camel@laptop>
	<plop87liw7n8t0.fsf@aoskar.kilobug.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit
Message-ID: <1310201594.2101.12.camel@laptop>
Mime-Version: 1.0

On Sat, 2011-07-09 at 10:43 +0200, Gael Le Mignot wrote:
> Hello Guillaume!
> 
> Sat, 09 Jul 2011 10:33:03 +0200, you wrote: 
> 
>  > 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?
> 
> maintenance_work_mem is at 16Mb, shared_buffers at 24Mb.
> 

IOW, default values.

> The server currently has  2Gb, we'll add more to it (it's  a VM), but we
> would like to be able to make  an estimate on how much memory it'll need
> for a  given rate of  INSERT into the  table, so we can  estimate future
> costs.
> 
>  >> 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.
> 
> So, it shouldn't  depend on data size ?

Nope, it shouldn't.

>  Is  there a fixed multiplicative
> factor between maintenance_work_mem and the memory actually used ?
> 

1 :)

>  >> 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.
> 
> Thanks, we'll try that.
> 

I don't quite understand how you can get up to 1GB used by your process.
According to your configuration, and unless I'm wrong, it shouldn't take
more than 40MB. Perhaps a bit more, but not 1GB. So, how did you find
this number?


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com