Re: Autovacuum running out of memory

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

 



On Wed, Aug 10, 2011 at 1:17 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
> Alexis Le-Quoc <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.

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

-- 
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