Re: Memory usage of auto-vacuum

From: Guillaume Lelarge
Subject: Re: Memory usage of auto-vacuum
Date: ,
Msg-id: 1310201594.2101.12.camel@laptop
(view: Whole thread, Raw)
In response to: Re: Memory usage of auto-vacuum  (Gael Le Mignot)
Responses: Re: Memory usage of auto-vacuum  (Gael Le Mignot)
List: pgsql-performance

Tree view

Memory usage of auto-vacuum  (Gael Le Mignot, )
 Re: Memory usage of auto-vacuum  (Craig Ringer, )
  Re: Memory usage of auto-vacuum  (Gael Le Mignot, )
 Re: Memory usage of auto-vacuum  (Guillaume Lelarge, )
  Re: Memory usage of auto-vacuum  (Gael Le Mignot, )
   Re: Memory usage of auto-vacuum  (Guillaume Lelarge, )
    Re: Memory usage of auto-vacuum  (Gael Le Mignot, )
     Re: Memory usage of auto-vacuum  (Guillaume Lelarge, )
      Re: Memory usage of auto-vacuum  (Gael Le Mignot, )
       Re: Memory usage of auto-vacuum  (Tom Lane, )
        Re: Memory usage of auto-vacuum  (Gael Le Mignot, )
   Re: Memory usage of auto-vacuum  (Craig Ringer, )
 Re: Memory usage of auto-vacuum  (Gael Le Mignot, )

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



pgsql-performance by date:

From: Craig Ringer
Date:
Subject: Re: Memory usage of auto-vacuum
From: Tom Lane
Date:
Subject: Re: Memory usage of auto-vacuum