From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | "David F(dot) Skoll" <dfs(at)roaringpenguin(dot)com> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Vacuuming and re-indexing (was Re: Vacuum meaning) |
Date: | 2003-01-03 23:39:09 |
Message-ID: | 200301032339.h03Nd9I23088@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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.
---------------------------------------------------------------------------
David F. Skoll wrote:
> On Fri, 3 Jan 2003, Robert Treat wrote:
>
> > 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
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
From | Date | Subject | |
---|---|---|---|
Next Message | Rajesh Kumar Mallah. | 2003-01-04 11:08:10 | Re: Get client's IP |
Previous Message | Tom Lane | 2003-01-03 20:48:47 | Re: Upgrade Procedures |