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@carax.com
>>> <mailto:cbuschini@carax.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