On Wed, Aug 10, 2011 at 1:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alexis Le-Quoc <alq@datadoghq.com> writes:
>> I've been hitting a "out of memory error" during autovacuum of
>> relatively large tables (compared to the amount of RAM available).
>
>> The error message is:
>> [10236]: [1-1] user=,db=,remote= ERROR: out of memory
>> [10236]: [2-1] user=,db=,remote= DETAIL: Failed on request of size 395973594.
>> [10236]: [3-1] user=,db=,remote= CONTEXT: automatic vacuum of table
>> "***.public.serialized_series"
>
>> --- postgresql.conf (subset) ----
>> shared_buffers = 1971421kB
>> work_mem = 9857kB
>> maintenance_work_mem = 752MB
>
> Since the memory map shows that not very much memory has been allocated
> by VACUUM yet, I suspect it's failing while trying to create the work
> array for remembering dead tuple TIDs. It will assume that it can use
> up to maintenance_work_mem for that. (The fact that it didn't ask for
> the whole 752MB probably means this is a relatively small table in
> which there couldn't possibly be that many TIDs.) So the short answer
> is "reduce maintenance_work_mem to something under 300MB".
>
> However, I find it a bit odd that you're getting this failure in what
> appears to be a 64-bit build. That means you're not running out of
> address space, so you must actually be out of RAM+swap. Does the
> machine have only 4GB or so of RAM? If so, that value for
> shared_buffers is unrealistically large; it's not leaving enough RAM for
> other purposes such as this.
The box has little under 8GB (it's on EC2, a "m1.large" instance)
total used free shared buffers cached
Mem: 7700 6662 1038 0 25 6078
-/+ buffers/cache: 558 7142
Swap: 0 0 0
There is no swap.
> Where did you get the above-quoted parameter settings, anyway? They
> seem a bit weird, as in written to many more decimal places than anyone
> could really expect to mean anything.
I have them computed by our configuration management system. Here's
the logic behind it (edited from ruby):
# Compute shared memory for procps
page_size = getconf PAGE_SIZE
phys_pages = getconf _PHYS_PAGES
shmall = phys_pages
shmmax = shmall * page_size
shared_buffers = kb_memory_total / 4
work_mem = (kb_memory_total / max_connections / 4)
maintenance_work_mem = (kb_memory_total * 100 / (1024 * 1024))
In turn they come from High-Performance Postgresql 9.0
(http://www.postgresql.org/about/news.1249)
Thanks,
--
Alexis Lê-Quôc