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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

I'm using pg 8.2.4 on Linux 2.6.15.

Jason

--
Jason Lustig
CTO, MavenHaven Inc.
www.mavenhaven.com
Where the Community Finds Wisdom

Israel: 054-231-8476
U.S.: 716-228-8729
Skype: jasonlustig


On Oct 16, 2007, at 7:45 AM, Richard Huxton wrote:

> 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

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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.

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?

Thanks,
Jason

--
Jason Lustig
Israel: 054-231-8476
U.S.: 716-228-8729
Skype: jasonlustig


On Oct 16, 2007, at 8:23 AM, Richard Huxton wrote:

> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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