Re: Vacuum analyse after a long time without one ...

Lewis Kapell a écrit :
> I think you've missed Tom's point.  You need to set maintenance_work_mem
> based on the physical capacity of your system.  If it (the parameter) is
> set too high, your operating system will encounter errors when trying to
> satisfy the requests that Postgres is making.
>
> Also as Tom just pointed out, there is a difference between work_mem and
> maintenance_work_mem.
>
> Thank you,
>
> Lewis Kapell
> Computer Operations
> Seton Home Study School
>
> -------------------------------------------------------
> For a free subscription to the Seton Home Study School monthly
> electronic newsletter, send an e-mail to newsletter-sub@setonhome.org
> -------------------------------------------------------
>
>
> Nicolas Michel wrote:
>> Tom Lane a écrit :
>>> Nicolas Michel <nicolas.michel@lemail.be> writes:
>>>> I have a problem with a database. The last full vacuum analyse was made
>>>> long time ago... So I tried to start launching a vacuum analyse and I
>>>> get this error :
>>>
>>>> $ vacuumdb -az
>>>> vacuumdb: vacuuming database "postgres"
>>>> VACUUM
>>>> vacuumdb: vacuuming database "mexi"
>>>> vacuumdb: vacuuming of database "mexi" failed: ERROR:  out of memory
>>>> DETAIL:  Failed on request of size 134697600.
>>>
>>> What is your maintenance_work_mem setting?  It rather looks like it's
>>> more than your system will really allow.
>>>
>>>             regards, tom lane
>>>
>> I already tried to set the work_mem setting to the max value I can but
>> it changed nothing.
>>
>
>
Yes, I made a mistake when replying to you. This is really
maintenance_work_mem that I modified and not work_mem.

I'm on postgres 8.1 on debian etch

- I have 16Go of RAM on that server (but 32bits OS with bigmem kernel ;
so I set shared buffer to 350000 (~2,7GB) for a shmmax of 4000000000
(~3,8GB)
- the first maintenance_work_mem value I was : 64396 (~62MB). It didn't
worked.
- the max value I tryied was 2080000 (~2Go). It didn't work neither.