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

Search Discussions

  • Craig Ringer at Jul 9, 2011 at 8:32 am

    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/
  • Gael Le Mignot at Jul 9, 2011 at 8:39 am
    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@pilotsystems.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
  • Guillaume Lelarge at Jul 9, 2011 at 8:33 am
    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.
  • Gael Le Mignot at Jul 9, 2011 at 8:43 am
    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@pilotsystems.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
  • Guillaume Lelarge at Jul 9, 2011 at 8:53 am

    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?
  • Gael Le Mignot at Jul 9, 2011 at 9:00 am
    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@pilotsystems.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
  • Guillaume Lelarge at Jul 9, 2011 at 9:06 am

    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?
  • Gael Le Mignot at Jul 9, 2011 at 9:27 am
    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@pilotsystems.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
  • Tom Lane at Jul 9, 2011 at 4:23 pm

    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
  • Gael Le Mignot at Jul 10, 2011 at 3:49 pm
    Hello Tom!

    Sat, 09 Jul 2011 12:23:18 -0400, you wrote:
    Gael Le Mignot <gael@pilotsystems.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@pilotsystems.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
  • Craig Ringer at Jul 9, 2011 at 12:15 pm

    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 at Jul 12, 2011 at 3:44 pm
    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@pilotsystems.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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedJul 9, '11 at 7:49a
activeJul 12, '11 at 3:44p
posts13
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase