Autovacuum running out of memory

From: Jason Lustig <lustig(at)brandeis(dot)edu>
To: pgsql-performance(at)postgresql(dot)org
Subject: Autovacuum running out of memory
Date: 2007-10-16 11:12:11
Message-ID: 374EB3D1-EE74-4B8C-9EEC-104ADD097568@brandeis.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Richard Huxton 2007-10-16 11:45:28 Re: Autovacuum running out of memory
Previous Message Stéphane Schildknecht 2007-10-16 09:50:51 Re: Vacuum goes worse