Re: Autovacuum running out of memory

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



=?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= <alq@xxxxxxxxxxxxx> 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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux