Обсуждение: 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.
Does autovacuum replace the need for a FULL vacuum please (to recover free space, etc)?
On 8/10/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
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.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
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.