Memory usage of auto-vacuum

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 - gael(at)pilotsystems(dot)net
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

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/

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

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 - gael(at)pilotsystems(dot)net
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

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 - gael(at)pilotsystems(dot)net
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

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

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 - gael(at)pilotsystems(dot)net
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

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

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 - gael(at)pilotsystems(dot)net
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

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/

Gael Le Mignot <gael(at)pilotsystems(dot)net> 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

Hello Tom!

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

> Gael Le Mignot <gael(at)pilotsystems(dot)net> 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 - gael(at)pilotsystems(dot)net
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

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 - gael(at)pilotsystems(dot)net
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