Re: Vacuum Problem

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 Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.