Re: Vacuum Problem

Brad Nicholson a écrit :
> On Wed, 2007-12-05 at 11:04 +0100, Cedric BUSCHINI wrote:
>
>> Usama Dar a écrit :
>>
>>> On Dec 5, 2007 12:53 PM, Cedric BUSCHINI <cbuschini(at)carax(dot)com
>>> <mailto:cbuschini(at)carax(dot)com>> wrote:
>>>
>>> Hello all,
>>>
>>> I have a problem. The following message keeps appearing in logs :
>>> ---------------------------------------------------------------------------------------------------------------------------
>>> WARNING: database "data_base" must be vacuumed within 2606182
>>> transactions
>>> HINT: To avoid a database shutdown, execute a full-database VACUUM in
>>> "data_base".
>>> ---------------------------------------------------------------------------------------------------------------------------
>>>
>>>
>>> Last week I have run : 'vacuumdb -vz -U postgres -ddata_base'
>>> Today it's still listed in 'ps aux'
>>> ---------------------------------------------------------------------------------------------------------------------------
>>>
>>> postgres 2113 1.0 1.9 26256 20132 ? D Nov26 130:14
>>> postgres: postgres data_base [local] VACUUM
>>> root 2146 0.0 0.1 4616 1508 tty1 Ss Nov28 0:00
>>> -bash
>>> root 2176 0.0 0.0 4648 996 tty1 S+ Nov28 0:00
>>> vacuumdb -vz -U postgres -ddata_base
>>> postgres 2177 0.0 0.3 10260 3572 ? S Nov28 7:12
>>> postgres: postgres data_base [local] VACUUM waiting
>>> ---------------------------------------------------------------------------------------------------------------------------
>>>
>>>
>>> In pg_stat_activity I can see two lines mentionning 'vacuum
>>> verbose analyze'
>>>
>>> What can I do ??
>>> I really need help
>>>
>>>
>>> it seems to me that you need to vacuum more frequently, and the hint
>>> seems to point you to a vacuum full for now, try "vacuumdb -avz", but
>>> beaware it can be time & resource consuming.
>>>
>> So your advice is to stop the running vacuum and run 'vacuum -avz' ?
>> My actual question is the running processes are doing something or not ?
>>
>>
>
> First off - what version of Postgres?
>
> My guess is, if you are getting the warning about the database needing
> to be vacuumed in x transactions, you probably have a fairly high
> traffic DB that is not getting vacuumed often enough. That means vacuum
> is going to have a whole lot of work to do. That's going to take a
> while.
>
> A couple of things to check.
>
> Do you have any of the vacuum_cost_delay stuff on? This will make
> vacuums go slower, but they will take less IO. If that is on, you might
> want to turn the values down or off, but be prepared to see your
> database IO usage go through the roof.
>
> What is your maintenance_work_mem set to? Bumping this value may
> increase the speed that your vacuum will run. You would have to restart
> the vacuum after changing the setting though.
>
>
Brad,

It's a 8.1.5
About these settings, these are both off ...
Should I turn them on ?

Because of the message, the database isn't used ...

--

Cedric BUSCHINI
- CARAX -
IT Department

Phone : + 33 1 4006 9864
fax : + 33 1 4006 9865