Thread: Memory usage of auto-vacuum

From:
Gael Le Mignot
Date:

Hello,

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.

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 ?

Also, is  there a way  to reduce that  memory usage ? Would  running the
autovacuum more frequently lower its memory usage ?

Regards,

--
Gaël Le Mignot - 
Pilot Systems - 9, rue Desargues - 75011 Paris
Tel : +33 1 44 53 05 55 - www.pilotsystems.net
Gérez vos contacts et vos newsletters : www.cockpit-mailing.com

From:
Craig Ringer
Date:

On 9/07/2011 3:25 PM, Gael Le Mignot wrote:
>
> Hello,
>
> 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.

What is maintenance_work_mem set to in postgresql.conf?

--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/

From:
Guillaume Lelarge
Date:

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


From:
Gael Le Mignot
Date:

Hello Craig!

Sat, 09 Jul 2011 16:31:47 +0800, you wrote:

 > On 9/07/2011 3:25 PM, Gael Le Mignot wrote:
 >>
 >> Hello,
 >>
 >> 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.

 > What is maintenance_work_mem set to in postgresql.conf?

It's the debian default, which is 16Mb. Do you think we should reduce it ?

I also forgot to add something which may be important : there are a lot
of INSERT (and SELECT) in those tables, but very few UPDATE/DELETE.

Regards,

--
Gaël Le Mignot - 
Pilot Systems - 9, rue Desargues - 75011 Paris
Tel : +33 1 44 53 05 55 - www.pilotsystems.net
Gérez vos contacts et vos newsletters : www.cockpit-mailing.com

From:
Gael Le Mignot
Date:

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.

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 ? Is  there a fixed multiplicative
factor between maintenance_work_mem and the memory actually used ?

 >> 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.

Regards,

--
Gaël Le Mignot - 
Pilot Systems - 9, rue Desargues - 75011 Paris
Tel : +33 1 44 53 05 55 - www.pilotsystems.net
Gérez vos contacts et vos newsletters : www.cockpit-mailing.com

From:
Guillaume Lelarge
Date:

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


From:
Gael Le Mignot
Date:

Hello Guillaume!

Sat, 09 Jul 2011 10:53:14 +0200, you wrote:

 > 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?

Looking at  "top" we  saw the postgres  process growing and  growing and
then shrinking  back, and doing  a "select * from  pg_stat_activity;" in
parallel of the growing we found only the "vacuum analyze" query running.

But maybe  we drawn the conclusion  too quickly, I'll  try disabling the
auto vacuum to see if we really get rid of the problem doing it.

Thanks for your answers.

Regards,

--
Gaël Le Mignot - 
Pilot Systems - 9, rue Desargues - 75011 Paris
Tel : +33 1 44 53 05 55 - www.pilotsystems.net
Gérez vos contacts et vos newsletters : www.cockpit-mailing.com

From:
Guillaume Lelarge
Date:

On Sat, 2011-07-09 at 11:00 +0200, Gael Le Mignot wrote:
> Hello Guillaume!
>
> Sat, 09 Jul 2011 10:53:14 +0200, you wrote:
>
>  > 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?
>
> Looking at  "top" we  saw the postgres  process growing and  growing and
> then shrinking  back, and doing  a "select * from  pg_stat_activity;" in
> parallel of the growing we found only the "vacuum analyze" query running.
>

There is not only one postgres process. So you first need to be sure
that it's the one that executes the autovacuum.

> But maybe  we drawn the conclusion  too quickly, I'll  try disabling the
> auto vacuum to see if we really get rid of the problem doing it.
>

Disabling the autovacuum is usually a bad idea. You'll have to execute
VACUUM/ANALYZE via cron, which could get hard to configure.

BTW, what's your PostgreSQL release? I assume at least 8.3 since you're
using FTS?


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


From:
Gael Le Mignot
Date:

Hello Guillaume!

Sat, 09 Jul 2011 11:06:16 +0200, you wrote:

 > On Sat, 2011-07-09 at 11:00 +0200, Gael Le Mignot wrote:
 >> Hello Guillaume!
 >>
 >> Sat, 09 Jul 2011 10:53:14 +0200, you wrote:
 >>
 >> > 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?
 >>
 >> Looking at  "top" we  saw the postgres  process growing and  growing and
 >> then shrinking  back, and doing  a "select * from  pg_stat_activity;" in
 >> parallel of the growing we found only the "vacuum analyze" query running.
 >>

 > There is not only one postgres process. So you first need to be sure
 > that it's the one that executes the autovacuum.

Shouldn't "pg_stat_activity" contain the current jobs of all the processes ?

 >> But maybe  we drawn the conclusion  too quickly, I'll  try disabling the
 >> auto vacuum to see if we really get rid of the problem doing it.
 >>

 > Disabling the autovacuum is usually a bad idea. You'll have to execute
 > VACUUM/ANALYZE via cron, which could get hard to configure.

Oh, yes, sure, I meant as a test  to know if it's the vacuum or not, not
to definitely disable it.

 > BTW, what's your PostgreSQL release? I assume at least 8.3 since you're
 > using FTS?

It's 8.4 from Debian Squeeze.

--
Gaël Le Mignot - 
Pilot Systems - 9, rue Desargues - 75011 Paris
Tel : +33 1 44 53 05 55 - www.pilotsystems.net
Gérez vos contacts et vos newsletters : www.cockpit-mailing.com

From:
Craig Ringer
Date:

On 9/07/2011 4:43 PM, Gael Le Mignot wrote:

> maintenance_work_mem is at 16Mb, shared_buffers at 24Mb.

Woah, what? And you're hitting a gigabyte for autovacuum? Yikes. That
just doesn't sound right.

Are you using any contrib modules? If so, which ones?

Are you able to post your DDL?

How big is the database? (Not that it should matter).

--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/

From:
Tom Lane
Date:

Gael Le Mignot <> writes:
> Sat, 09 Jul 2011 11:06:16 +0200, you wrote:
>>> BTW, what's your PostgreSQL release? I assume at least 8.3 since you're
>>> using FTS?

> It's 8.4 from Debian Squeeze.

8.4.what?

In particular I'm wondering if you need this 8.4.6 fix:
http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=f0e4331d04fa007830666c5baa2c3e37cce9c3ff

            regards, tom lane

From:
Gael Le Mignot
Date:

Hello Tom!

Sat, 09 Jul 2011 12:23:18 -0400, you wrote:

 > Gael Le Mignot <> writes:
 >> Sat, 09 Jul 2011 11:06:16 +0200, you wrote:
 >>>> BTW, what's your PostgreSQL release? I assume at least 8.3 since you're
 >>>> using FTS?

 >> It's 8.4 from Debian Squeeze.

 > 8.4.what?

It's 8.4.8-0squeeze1

 > In particular I'm wondering if you need this 8.4.6 fix:
 > http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=f0e4331d04fa007830666c5baa2c3e37cce9c3ff

Thanks for the tip, it very well could have been that, but it's 8.4.8, I
checked the concerned  source file and the patch is  there, and I didn't
find any Debian-specific patch that could collide with it.

Regards,

--
Gaël Le Mignot - 
Pilot Systems - 9, rue Desargues - 75011 Paris
Tel : +33 1 44 53 05 55 - www.pilotsystems.net
Gérez vos contacts et vos newsletters : www.cockpit-mailing.com

From:
Gael Le Mignot
Date:

Hello,

Here is an update on my problem :

- the problem was caused by "VACUUM ANALYZE", but by a plain "VACUUM" ;

- it was exactly the same with manual and automatic "VACUUM ANALYZE" ;

- it was caused by a GIN index on a tsvector, using a very high (10000)
  statistics target.

Setting back the statistics to 1000  reduced the amount of RAM used to a
very reasonable amount.

The value of 10000 is indeed  not very realistic, but I think that would
deserve some mention on the  documentation, if possible with an estimate
of  the maximal memory  usage for  a given  statistics target  and table
size.

Do  you think  it's a  good idea,  and if  so, if  that estimate  can be
reasonably made ?

Regards,

--
Gaël Le Mignot - 
Pilot Systems - 9, rue Desargues - 75011 Paris
Tel : +33 1 44 53 05 55 - www.pilotsystems.net
Gérez vos contacts et vos newsletters : www.cockpit-mailing.com