Re: Autovacuum running out of memory

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Autovacuum running out of memory
Дата
Msg-id 13854.1312996666@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Autovacuum running out of memory  (Alexis Lê-Quôc <alq@datadoghq.com>)
Ответы Re: Autovacuum running out of memory  (Alexis Lê-Quôc <alq@datadoghq.com>)
Список pgsql-performance
=?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= <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.

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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tomas Vondra
Дата:
Сообщение: Re: poor pefrormance with regexp searches on large tables
Следующее
От: "Kevin Grittner"
Дата:
Сообщение: Re: poor pefrormance with regexp searches on large tables