Re: Vacuum Problem - Mailing list pgsql-novice

On Fri, 2007-12-07 at 10:30 +0100, Cedric BUSCHINI wrote:
> 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 ?

No.  Turning them on will make your vacuum go slower.  You don't want
this.

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

First, what is your maintenance_work_mem set to?  If you have a decent
amount of memory, you'll want to try setting it high while doing this
vacuum - to something like 100000.  Setting this value up and re-running
the vacuum might get you past this problem.

If you pipe the output from your vacuum command to a file, you'll be
able to see what it is doing.  It would also be really useful for folks
to help you troubleshoot the problem if you could post the verbose
vacuum output so we could see exactly what vacuum is working on.

However, If my earlier theory is correct - lots of updates/deletes and
not enough routine vacuuming being done, then you have a whole lot of
dead tuples in that database.  Which presents a different problem -
database bloat.

Ideally, you would want to identify if your tables are bloated or not
and act accordingly.  The output from vacuum would tell tell this.

However, if the database is not being used, then you have another other
option.

You can use the cluster command to clear out the dead tuples a lot
quicker that the vacuum will.  Cluster will take an access exclusive
lock on the table, and psychically reorganize the data on the disk.  It
will also get rid of all the dead tuples, and compact the physical
layout of your DB.  You will still need to vacuum the whole database to
deal with the "You must vacuum the database" issue, but the vacuum would
go faster.

Check out the documentation for cluster
http://www.postgresql.org/docs/8.1/interactive/sql-cluster.html

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.