Thread: Autovacuum running out of memory

Whenever I turn on Autovacuum on my database, I get a ton of error
messages like this in my Postgres log:

Oct 16 06:43:47 [2897]: [1-1]  user=,db= ERROR:  out of memory
Oct 16 06:43:47 [2897]: [1-2]  user=,db= DETAIL:  Failed on request
of size 524287998.

It always fails on the same request. When I turn off autovacuum, they
go away. However, when I run VACUUM FULL manually, I don't get this
error.

My server has 2gb of ram, and my postgres settings are:

autovacuum = on                         # enable autovacuum subprocess?
                                         # 'on' requires
stats_start_collector
                                         # and stats_row_level to
also be on
#autovacuum_naptime = 1min              # time between autovacuum runs
#autovacuum_vacuum_threshold = 500      # min # of tuple updates before
                                         # vacuum
#autovacuum_analyze_threshold = 250     # min # of tuple updates before
                                         # analyze
#autovacuum_vacuum_scale_factor = 0.2   # fraction of rel size before
                                         # vacuum
#autovacuum_analyze_scale_factor = 0.1  # fraction of rel size before
                                         # analyze
#autovacuum_freeze_max_age = 200000000  # maximum XID age before
forced vacuum
                                         # (change requires restart)
#autovacuum_vacuum_cost_delay = -1      # default vacuum cost delay for
                                         # autovacuum, -1 means use
                                         # vacuum_cost_delay
autovacuum_vacuum_cost_limit = -1       # default vacuum cost limit for
                                         # autovacuum, -1 means use
                                         # vacuum_cost_limit

shared_buffers = 20000          # min 128kB or max_connections*16kB
                                         # (change requires restart)
#temp_buffers = 8MB                     # min 800kB
#max_prepared_transactions = 5          # can be 0 or more
                                         # (change requires restart)
# Note: increasing max_prepared_transactions costs ~600 bytes of
shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 4096                         # min 64kB
maintenance_work_mem = 500MB            # min 1MB
#max_stack_depth = 2MB                  # min 100kB


Any ideas as to what might be going on?

Thanks
Jason

Not really a performance question, but...

Jason Lustig wrote:
> Whenever I turn on Autovacuum on my database, I get a ton of error
> messages like this in my Postgres log:
>
> Oct 16 06:43:47 [2897]: [1-1]  user=,db= ERROR:  out of memory
> Oct 16 06:43:47 [2897]: [1-2]  user=,db= DETAIL:  Failed on request of
> size 524287998.
>
> It always fails on the same request. When I turn off autovacuum, they go
> away. However, when I run VACUUM FULL manually, I don't get this error.

Is there nothing before this giving the error message some context?
Is the user and database really blank, or have you just trimmed those?
What version of PG is this, and running on what O.S.?

--
   Richard Huxton
   Archonet Ltd

There isn't any more error message than this... it simply repeats every minute or so, which is really quite strange. And the user & db is really blank in the log, I didn't trim it (if I did I would have replaced it with dummy values).

Jason Lustig wrote:
> There isn't any more error message than this... it simply repeats every
> minute or so, which is really quite strange. And the user & db is really
> blank in the log, I didn't trim it (if I did I would have replaced it
> with dummy values).

Hmm - odd that you're not getting any connection details.

> I'm using pg 8.2.4 on Linux 2.6.15.

Fair enough.

>>> Oct 16 06:43:47 [2897]: [1-1]  user=,db= ERROR:  out of memory
>>> Oct 16 06:43:47 [2897]: [1-2]  user=,db= DETAIL:  Failed on request
>>> of size 524287998.

Well, since this is about 500MB and your maintenance_work_mem is set to
500MB that's the obvious place to start. It might just be that you've
not got enough free memory.

What happens if you set maintenance_work_mem to say 50MB?

--
   Richard Huxton
   Archonet Ltd

I lowered the maintenance_work_mem to 50MB and am still getting the same errors:

Jason Lustig wrote:
> I lowered the maintenance_work_mem to 50MB and am still getting the same
> errors:
>
> Oct 16 09:26:57 [16402]: [1-1]  user=,db= ERROR:  out of memory
> Oct 16 09:26:57 [16402]: [1-2]  user=,db= DETAIL:  Failed on request of
> size 52428798.
> Oct 16 09:27:57 [16421]: [1-1]  user=,db= ERROR:  out of memory
> Oct 16 09:27:57 [16421]: [1-2]  user=,db= DETAIL:  Failed on request of
> size 52428798.
> Oct 16 09:29:44 [16500]: [1-1]  user=,db= ERROR:  out of memory
> Oct 16 09:29:44 [16500]: [1-2]  user=,db= DETAIL:  Failed on request of
> size 52428798.

Hmm - it's now failing on a request of 50MB, which shows it is in fact
maintenance_work_mem that's the issue.

> Looking at my free memory (from TOP) I find
>
> Mem:   2062364k total,  1846696k used,   215668k free,   223324k buffers
> Swap:  2104496k total,      160k used,  2104336k free,   928216k cached
>
> So I don't think that I'm running out of memory total... it seems like
> it's continually trying to do it. Is there a reason why Postgres would
> be doing something without a username or database? Or is that just how
> autovacuum works?

I've not seen an error at startup before, but if it's not connected yet
then that would make sense.

I'm guessing this is a per-user limit that the postgres user is hitting.
If you "su" to user postgres and run "ulimit -a" that should show you if
you have any limits defined. See "man bash" for more details on ulimit.


--
   Richard Huxton
   Archonet Ltd

On 10/16/07, Jason Lustig <lustig@brandeis.edu> wrote:

> Looking at my free memory (from TOP) I find
>
> Mem:   2062364k total,  1846696k used,   215668k free,   223324k buffers
> Swap:  2104496k total,      160k used,  2104336k free,   928216k cached
>
> So I don't think that I'm running out of memory total... it seems like it's
> continually trying to do it. Is there a reason why Postgres would be doing
> something without a username or database? Or is that just how autovacuum
> works?

You are NOT running out of memory.  Look at the size of your cache and
buffers, together they add up to over 1 Gig of memory.  You've got
plenty of free memory.

I'm betting you're running postgresql under an account with a ulimit
setting on your memory.

I ran "ulimit -a" for the postgres user, and here's what I got:

Jason Lustig wrote:
> I ran "ulimit -a" for the postgres user, and here's what I got:

> max memory size         (kbytes, -m) 200000
> virtual memory          (kbytes, -v) 200000

There you go - you probably are exceeding these.

Add some lines to /etc/security/limits.conf to increase them.

--
   Richard Huxton
   Archonet Ltd

On 10/16/07, Jason Lustig <lustig@brandeis.edu> wrote:
> I ran "ulimit -a" for the postgres user, and here's what I got:
>
> core file size          (blocks, -c) 200000
> data seg size           (kbytes, -d) 200000
> max nice                        (-e) 0
> file size               (blocks, -f) unlimited
> pending signals                 (-i) 32635
> max locked memory       (kbytes, -l) 32
> max memory size         (kbytes, -m) 200000
> open files                      (-n) 100
> pipe size            (512 bytes, -p) 8
> POSIX message queues     (bytes, -q) 819200
> max rt priority                 (-r) 0
> stack size              (kbytes, -s) 8192
> cpu time               (seconds, -t) unlimited
> max user processes              (-u) 100
> virtual memory          (kbytes, -v) 200000
> file locks                      (-x) unlimited

There ya go.  it's limited to 200M memory.

Generally speaking, limiting postgresql to something that small is not
a good idea.  Set it to ~ 1 Gig or so and see how it works.

On Tue, 2007-10-16 at 10:14 -0400, Jason Lustig wrote:
> I ran "ulimit -a" for the postgres user, and here's what I got:
...
> max memory size         (kbytes, -m) 200000
> open files                      (-n) 100
> max user processes              (-u) 100
> virtual memory          (kbytes, -v) 200000
...

These settings are all quite low for a dedicated database server, they
would be more appropriate for a small development instance of PG sharing
a machine with several other processes.

Others have commented on the memory settings, but depending on the
maximum number of connections you expect to have open at any time you
may want to consider increasing the max user processes and open files
settings as well.

-- Mark Lewis

On Oct 16, 2007, at 10:22 AM, Richard Huxton wrote:

> Add some lines to /etc/security/limits.conf to increase them.

Sorry for being somewhat of a linux novice -- but what is the best
way to do this? It doesn't seem to provide matching options from
ulimit to the limits.conf file.

Thanks,
Jason

Jason Lustig escribió:
> On Oct 16, 2007, at 10:22 AM, Richard Huxton wrote:
>
>> Add some lines to /etc/security/limits.conf to increase them.
>
> Sorry for being somewhat of a linux novice -- but what is the best way
> to do this? It doesn't seem to provide matching options from ulimit to
> the limits.conf file.
>
> Thanks,
> Jason
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
http://www.userlocal.com/security/secpam.php

Richard Huxton <dev@archonet.com> writes:
> Hmm - odd that you're not getting any connection details.

Not really; the autovacuum process doesn't have any connection, so those
log_line_prefix fields will be left empty.  The weird thing about this
is why the large maintenance_work_mem works for a regular session and
not for autovacuum.  There really shouldn't be much difference in the
maximum workable setting for the two cases, AFAICS.

Your later suggestion to check out the postgres user's ulimit -a
settings seems like the appropriate next step, but I'm not seeing
how ulimit would affect only some of the postmaster's children.

            regards, tom lane

I wrote:
> ... The weird thing about this
> is why the large maintenance_work_mem works for a regular session and
> not for autovacuum.  There really shouldn't be much difference in the
> maximum workable setting for the two cases, AFAICS.

After re-reading the thread I realized that the OP is comparing manual
VACUUM FULL to automatic plain VACUUM, so the mystery is solved.
Plain VACUUM tries to grab a maintenance_work_mem-sized array of
tuple IDs immediately at command startup.  VACUUM FULL doesn't work
like that.

Given the 200M ulimit -v, and the shared_buffers setting of 20000
(about 160M), the behavior is all explained if we assume that shared
memory counts against -v.  Which I think it does.

            regards, tom lane

How about shared memory related settings of your kernel? ie.
kernel.shmall, kernel.shmmax . Have a check with sysctl, maybe they
should be raised:

http://www.postgresql.org/docs/8.2/interactive/kernel-resources.html

Regards

Jason Lustig wrote:
> I ran "ulimit -a" for the postgres user, and here's what I got:
>
> core file size          (blocks, -c) 200000
> data seg size           (kbytes, -d) 200000
> max nice                        (-e) 0
> file size               (blocks, -f) unlimited
> pending signals                 (-i) 32635
> max locked memory       (kbytes, -l) 32
> max memory size         (kbytes, -m) 200000
> open files                      (-n) 100
> pipe size            (512 bytes, -p) 8
> POSIX message queues     (bytes, -q) 819200
> max rt priority                 (-r) 0
> stack size              (kbytes, -s) 8192
> cpu time               (seconds, -t) unlimited
> max user processes              (-u) 100
> virtual memory          (kbytes, -v) 200000
> file locks                      (-x) unlimited
>
>
>
> --
> Jason Lustig
> Israel: 054-231-8476
> U.S.: 716-228-8729
> Skype: jasonlustig
>
>
> On Oct 16, 2007, at 10:01 AM, Richard Huxton wrote:
>
>> Jason Lustig wrote:
>>> I lowered the maintenance_work_mem to 50MB and am still getting the
>>> same errors:
>>> Oct 16 09:26:57 [16402]: [1-1]  user=,db= ERROR:  out of memory
>>> Oct 16 09:26:57 [16402]: [1-2]  user=,db= DETAIL:  Failed on request
>>> of size 52428798.
>>> Oct 16 09:27:57 [16421]: [1-1]  user=,db= ERROR:  out of memory
>>> Oct 16 09:27:57 [16421]: [1-2]  user=,db= DETAIL:  Failed on request
>>> of size 52428798.
>>> Oct 16 09:29:44 [16500]: [1-1]  user=,db= ERROR:  out of memory
>>> Oct 16 09:29:44 [16500]: [1-2]  user=,db= DETAIL:  Failed on request
>>> of size 52428798.
>>
>> Hmm - it's now failing on a request of 50MB, which shows it is in fact
>> maintenance_work_mem that's the issue.
>>
>>> Looking at my free memory (from TOP) I find
>>> Mem:   2062364k total,  1846696k used,   215668k free,   223324k buffers
>>> Swap:  2104496k total,      160k used,  2104336k free,   928216k cached
>>> So I don't think that I'm running out of memory total... it seems
>>> like it's continually trying to do it. Is there a reason why Postgres
>>> would be doing something without a username or database? Or is that
>>> just how autovacuum works?
>>
>> I've not seen an error at startup before, but if it's not connected
>> yet then that would make sense.
>>
>> I'm guessing this is a per-user limit that the postgres user is
>> hitting. If you "su" to user postgres and run "ulimit -a" that should
>> show you if you have any limits defined. See "man bash" for more
>> details on ulimit.
>>
>>
>> --
>>   Richard Huxton
>>   Archonet Ltd
>