Обсуждение: vacuumdb exclude tables option?

Hi,

I have a database that includes both highly transactional tables and
archive tables - OLTP and OLAP mixed together.  Some of the archival
tables, which only experience inserts and reads, not updates or
deletes, contain many millions of rows and so they take a *long* time
to vacuum.  Is there currently any means to exclude only these tables
from vacuumdb operations other than explicitly vacuuming all the
other tables and explicity skipping the archival tables?

If not, are there any plans to add a command line switch to vacuumdb
to exclude tables from it's operation, ala 'vacuumdb -d mytest -x
archive_tab1 -x archive_tab2 -x archive_tab3'?  I searched the list
archives but didn't see anything about being able to do this.
Something like this would save many hours of useless vacuuming in
this particular setup.

TIA,
Joel

On Wed, 2006-08-09 at 13:24, Joel Stevenson wrote:
> Hi,
>
> I have a database that includes both highly transactional tables and
> archive tables - OLTP and OLAP mixed together.  Some of the archival
> tables, which only experience inserts and reads, not updates or
> deletes, contain many millions of rows and so they take a *long* time
> to vacuum.  Is there currently any means to exclude only these tables
> from vacuumdb operations other than explicitly vacuuming all the
> other tables and explicity skipping the archival tables?
>
> If not, are there any plans to add a command line switch to vacuumdb
> to exclude tables from it's operation, ala 'vacuumdb -d mytest -x
> archive_tab1 -x archive_tab2 -x archive_tab3'?  I searched the list
> archives but didn't see anything about being able to do this.
> Something like this would save many hours of useless vacuuming in
> this particular setup.

Does autovacuum fail you in this quest?  I've found it's pretty good
about leaving along tables that don't need vacuuming.  It's usually the
other way around that people have problems with, autovacuum NOT
vacuuming tables that need vacuuming often enough.

On Wed, 2006-08-09 at 23:01, adey wrote:
> Does autovacuum replace the need for a FULL vacuum please (to recover
> free space, etc)?

In most cases, yes.  The idea is that autovacuum should put the database
into a "steady state" where there is some % of each table that is free
space and being recycled without the table growing or shrinking due to
vacuuming.

But it doesn't always work out that way.  If you're updating the table
very quickly, then you might outrun auto vacuum and get into a sticky
situation.

The somewhat bad situation is that your FSM settings are high enough to
store all the empty bits for reuse, but there's a LOT of them in some
often updated table.  That's a bit bothersome.

The worst case scenario is that your FSM settings are not high enough,
and you've not got a table which has an increasing number of empty
spaces in it, even though the amount of data is steady state.

Generally, there are autovacuum settings and fsm settings that will keep
this from happening, but once you've got bloat, the only fix is vacuum
full, cluster, or reindex.  I.e. something that copies the data into a
new file for you.  All three of those are nasty in terms of the locks
they have to take out, and they block other access on the table while
running.

Thanks for the reply.  I'm still playing around a bit with my
autovacuum settings and the manual vaccum runs I'm making are part of
an effort to better understand the just what sort of settings I need
- I'm running vacuumdb with verbose output, etc.

Since I know that these archive tables are append only tables I was
just hoping there was a way to skip them more easily and it seemed
like it might be something that was not unique to my situation.

On Thu, 2006-08-10 at 09:44, Joel Stevenson wrote:
> Thanks for the reply.  I'm still playing around a bit with my
> autovacuum settings and the manual vaccum runs I'm making are part of
> an effort to better understand the just what sort of settings I need
> - I'm running vacuumdb with verbose output, etc.
>
> Since I know that these archive tables are append only tables I was
> just hoping there was a way to skip them more easily and it seemed
> like it might be something that was not unique to my situation.

Something you can look at doing it running autovacuum with different
settings depending on the time of day.  That way, with more aggresive
settings it will trigger on those less used tables during off hours and
during the day (hopefully) leave them alone all the time.

Note that autovacuum also runs analyzes on your tables as needed as
well, so even if those archive tables don't need vacuuming, they might
well need analyzing.  You can adjust the settings for those things
separately, btw.

On Wed, Aug 09, 2006 at 11:24:03 -0700,
  Joel Stevenson <joelstevenson@mac.com> wrote:
> I have a database that includes both highly transactional tables and
> archive tables - OLTP and OLAP mixed together.  Some of the archival
> tables, which only experience inserts and reads, not updates or
> deletes, contain many millions of rows and so they take a *long* time
> to vacuum.  Is there currently any means to exclude only these tables
> from vacuumdb operations other than explicitly vacuuming all the
> other tables and explicity skipping the archival tables?

Take a look at:
http://developer.postgresql.org/docs/postgres/routine-vacuuming.html#AUTOVACUUM
There will be a way to have per table settings in 8.2, though the interface
for setting things up is klunky.
I also seem to remember some discussion about having a separate transaction
for each table being cleaned as a new feature. This helps with some other
problems. There will still be an issue if you mix some long running queries
with frequent updates in the same database, as the long running queries can
prevent recovering a lot of rows in frequently updated tables. I have seen
discussions of people brainstorming about this problem, but I don't remember
anyone coming up with a great solution.

Bruno Wolff III wrote:
> On Wed, Aug 09, 2006 at 11:24:03 -0700,
>   Joel Stevenson <joelstevenson@mac.com> wrote:
> > I have a database that includes both highly transactional tables and
> > archive tables - OLTP and OLAP mixed together.  Some of the archival
> > tables, which only experience inserts and reads, not updates or
> > deletes, contain many millions of rows and so they take a *long* time
> > to vacuum.  Is there currently any means to exclude only these tables
> > from vacuumdb operations other than explicitly vacuuming all the
> > other tables and explicity skipping the archival tables?
>
> Take a look at:
> http://developer.postgresql.org/docs/postgres/routine-vacuuming.html#AUTOVACUUM
> There will be a way to have per table settings in 8.2, though the interface
> for setting things up is klunky.
> I also seem to remember some discussion about having a separate transaction
> for each table being cleaned as a new feature.

Hmm, actually a separate transaction is being used for each table since
about forever.

What's new in 8.2 is the ability to ignore other long-running vacuums.
(I think it will help Joel, in that vacuuming the archival tables will
not stop dead tuples from being dropped from the "OLTP tables", though
long-running SELECTs on the OLAP tables will continue to do so.)

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.