Re: Autovacuum running out of memory

=?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= <alq(at)datadoghq(dot)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.

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.

regards, tom lane