Vacuuming and re-indexing (was Re: Vacuum meaning) 
Author Message
 Vacuuming and re-indexing (was Re: Vacuum meaning)

Quote:

> The purpose of a regular aka "lazy" vacuum is to mark dead tuples
> generated from updates and deletions as reusable by future inserts and
> updates. It doesn't recover any disk space, but allows currently wasted
> space to be reused. "Full" vacuums, otoh, serve the purpose of
> reclaiming unused space.

The whole "vaccuum" concept is, IMHO, one of the weakest aspects of
PostgreSQL.  It really makes it difficult to build products around
PostgreSQL unless you have a good idea of the database churn, which
isn't always possible.

Is there any way to adaptively vacuum the database?  By this, I mean
is it possible to run a query every few minutes and decide to vacuum
based on the query results?

Quote:
> However, based on your 4:00 output, I'd say you need to start doing more
> frequent lazy vacuums on user_logs_digest and user_traffic. Try and
> determine how long it takes for 15% of those tables to be updated or
> delete/insert and then set a cron task to do a lazy vacuum analyze at
> that frequency. (This is in additional to regular vacuuming on the rest
> of the database)

Is there any query that can return that info?  A query that tells how
many dead tuples there are?

Quote:
> It's possible your suffering index bloat as well and you might need to
> do some regular reindexing,

Again, this sounds ugly.  How do you determine this?  How do you get your
database to be reindexed as often as needed, but not too often?

--
David.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Tue, 21 Jun 2005 23:19:44 GMT
 Vacuuming and re-indexing (was Re: Vacuum meaning)



Quote:
> > It's possible your suffering index bloat as well and you might need to
> > do some regular reindexing,

> Again, this sounds ugly.  How do you determine this?  How do you get your
> database to be reindexed as often as needed, but not too often?

Sound more ugly considering that some times the reindex is not possible
if the server is stressed ( the reindex hang ) for my experience
instead of do a reindex is better delete the indexes and recreate them.

With the following query you can obtain the size of each table or index:

SELECT relname, relpages * 8 AS Mb, reltuples
FROM pg_class
ORDER BY relpages DESC
LIMIT 10;

If you use the 7.3 I think you should multiply for 11.

Ciao
Gaetano.



Wed, 22 Jun 2005 01:22:00 GMT
 Vacuuming and re-indexing (was Re: Vacuum meaning)

No question vacuuming is ugly.  My hope is that some of the statistics
collection can be used to automatic vacuum for 7.4.  We have some folks
working on that.

---------------------------------------------------------------------------

Quote:


> > The purpose of a regular aka "lazy" vacuum is to mark dead tuples
> > generated from updates and deletions as reusable by future inserts and
> > updates. It doesn't recover any disk space, but allows currently wasted
> > space to be reused. "Full" vacuums, otoh, serve the purpose of
> > reclaiming unused space.

> The whole "vaccuum" concept is, IMHO, one of the weakest aspects of
> PostgreSQL.  It really makes it difficult to build products around
> PostgreSQL unless you have a good idea of the database churn, which
> isn't always possible.

> Is there any way to adaptively vacuum the database?  By this, I mean
> is it possible to run a query every few minutes and decide to vacuum
> based on the query results?

> > However, based on your 4:00 output, I'd say you need to start doing more
> > frequent lazy vacuums on user_logs_digest and user_traffic. Try and
> > determine how long it takes for 15% of those tables to be updated or
> > delete/insert and then set a cron task to do a lazy vacuum analyze at
> > that frequency. (This is in additional to regular vacuuming on the rest
> > of the database)

> Is there any query that can return that info?  A query that tells how
> many dead tuples there are?

> > It's possible your suffering index bloat as well and you might need to
> > do some regular reindexing,

> Again, this sounds ugly.  How do you determine this?  How do you get your
> database to be reindexed as often as needed, but not too often?

> --
> David.

> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
  Bruce Momjian                        |  http://candle.pha.pa.us

  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Wed, 22 Jun 2005 07:39:47 GMT
 Vacuuming and re-indexing (was Re: Vacuum meaning)

Quote:

> Is there any way to adaptively vacuum the database?  By this, I mean is
> it possible to run a query every few minutes and decide to vacuum based
> on the query results?

You may want to investigate this:

http://gborg.postgresql.org/project/pgavd/projdisplay.php

I started to, but haven't had the time to really dig into it.

--
Jeff Boes                                      vox 616.226.9550 ext 24
Database Engineer                                     fax 616.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise



Tue, 28 Jun 2005 05:25:57 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Vacuum and Vacuum analyse

2. VACUUM vs VACUUM ANALYZE

3. Vacuum return codes (vacuum as db integrity check?)

4. Question regarding effects of Vacuum, Vacuum Analyze, and Reindex

5. VACUUM AND VACUUM ANALYSE

6. Still confused about VACUUM vs. VACUUM FULL

7. Vacuum VS Vacuum Analyze

8. Meaning of vacuum output

9. Vacuum meaning

10. Index Scans become Seq Scans after VACUUM ANALYSE

11. Index Scans become Seq Scans after VACUUM ANALYSE

12. Index Scans become Seq Scans after VACUUM ANALYSE


 
Powered by phpBB® Forum Software