From: | peter(at)vfemail(dot)net |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Vacuuming -- again |
Date: | 2011-01-20 21:50:05 |
Message-ID: | 20110120215127.87A8A1337BD5@mail.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
At 04:06 PM 1/20/2011, Tom Lane wrote:
>peter(at)vfemail(dot)net writes:
>> I posted a message about PostgreSQL vacuuming to this list in Feb. 2010, received some responses, but probably didn't understand what I was being told at that time.
>
>> There's a script running on my server hosting a PostgreSQL database that does some type of vacuuming routine once each week. Specifically, the script executes this command:
>
>> psql -d database_name -c "vacuum full verbose"
>
>> and e-mails the verbose output to me.
>
>FULL vacuum once a week is a pretty terrible maintenance procedure.
>It locks your DB and it isn't that effective; in particular, your
>indexes are probably enormously bloated by now, which is why things
>are getting slower.
>
>What you should be doing is plain (non full) vacuums on a much more
>frequent schedule. Or even better, let autovacuum do it for you,
>if you're on a recent enough PG version to have a decent autovacuum
>built in (8.4 and up do it pretty well).
>
>Right now, you probably need a one-time REINDEX to clean up the mess
>from overuse of VACUUM FULL. You'll want to schedule that in whatever
>maintenance slot you're using for the current VACUUM FULL run.
>
>There's much more extensive discussion of this stuff in the "routine
>maintenance" chapter of the PG manual. See
>http://www.postgresql.org/docs/8.4/static/maintenance.html
>(adjust link in the obvious spot depending on which PG release
>you are running).
>
> regards, tom lane
>
>--
>Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-novice
Confession time. I'm not running PostgreSQL 8.anything. I'm still running PostgreSQL version 7.4.3 on a Dell PowerEdge SC400 using FreeBSD 4.10. Do all of the concepts you relate still apply? I think I did a REINDEX operating a couple of months ago. Will a:L
psql -d database_name -c "reindex"
command do the trick to reindex everything?
Thank you for your patience.
-------------------------------------------------
This message sent via VFEmail.net
http://www.vfemail.net
$14.95 Lifetime accounts! 15GB disk! No bandwidth quotas!
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-01-20 23:31:25 | Re: Vacuuming -- again |
Previous Message | Tom Lane | 2011-01-20 21:06:11 | Re: Vacuuming -- again |