Thread: Vacuuming -- again
I posted a message about PostgreSQL vacuuming to this list in Feb. 2010, received some responses, but probably didn't
understandwhat 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.
One response said that FULL vacuuming on a routine basis was a very bad idea.
I'm wondering now if I should delete the "full" parameter and change this script to say:
psql -d database_name -c "vacuum verbose"
The vacuuming routine is taking longer and longer each week. Currently, it takes about 30 hours for the routine to
complete. During that time, the database is not accessible.
Thanks in advance for your guidance.
-------------------------------------------------
This message sent via VFEmail.net
http://www.vfemail.net
$14.95 Lifetime accounts! 15GB disk! No bandwidth quotas!
On Thu, Jan 20, 2011 at 2:34 PM, <peter@vfemail.net> wrote: > > I posted a message about PostgreSQL vacuuming to this list in Feb. 2010, received some responses, but probably didn't understandwhat 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 eachweek. Specifically, the script executes this command: > > psql -d database_name -c "vacuum full verbose" > > and e-mails the verbose output to me. > > One response said that FULL vacuuming on a routine basis was a very bad idea. > > I'm wondering now if I should delete the "full" parameter and change this script to say: > > psql -d database_name -c "vacuum verbose" > > The vacuuming routine is taking longer and longer each week. Currently, it takes about 30 hours for the routine to complete. During that time, the database is not accessible. > > Thanks in advance for your guidance. > Peter, Right from the PostgreSQL website documentation http://www.postgresql.org/docs/8.1/static/maintenance.html "The second form is the VACUUM FULL command. This uses a more aggressive algorithm for reclaiming the space consumed by expired row versions. Any space that is freed by VACUUM FULL is immediately returned to the operating system. Unfortunately, this variant of the VACUUM command acquires an exclusive lock on each table while VACUUM FULL is processing it. Therefore, frequently using VACUUM FULL can have an extremely negative effect on the performance of concurrent database queries. The standard form of VACUUM is best used with the goal of maintaining a fairly level steady-state usage of disk space. If you need to return disk space to the operating system you can use VACUUM FULL — but what's the point of releasing disk space that will only have to be allocated again soon? Moderately frequent standard VACUUM runs are a better approach than infrequent VACUUM FULL runs for maintaining heavily-updated tables. Recommended practice for most sites is to schedule a database-wide VACUUM once a day at a low-usage time of day, supplemented by more frequent vacuuming of heavily-updated tables if necessary. (Some installations with an extremely high rate of data modification VACUUM busy tables as often as once every few minutes.) If you have multiple databases in a cluster, don't forget to VACUUM each one; the program vacuumdb may be helpful. VACUUM FULL is recommended for cases where you know you have deleted the majority of rows in a table, so that the steady-state size of the table can be shrunk substantially with VACUUM FULL's more aggressive approach. Use plain VACUUM, not VACUUM FULL, for routine vacuuming for space recovery. " A regular vacuum does not require exclusive access to the database. If you are not running a routine analyze task, that would be helpful too. http://www.postgresql.org/docs/8.1/static/maintenance.html#VACUUM-FOR-STATISTICS The other things you can do for performance are reindex, cluster, adding indexes, and changing the table statistics. But all I can do is point you to the documentation for them, I haven't used them enough to be able to give any useful recommendations. I hope that helps. -Mike Swierczek > > ------------------------------------------------- > This message sent via VFEmail.net > http://www.vfemail.net > $14.95 Lifetime accounts! 15GB disk! No bandwidth quotas! > > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice >
peter@vfemail.net writes: > I posted a message about PostgreSQL vacuuming to this list in Feb. 2010, received some responses, but probably didn't understandwhat 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 eachweek. 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
At 04:06 PM 1/20/2011, Tom Lane wrote: >peter@vfemail.net writes: >> I posted a message about PostgreSQL vacuuming to this list in Feb. 2010, received some responses, but probably didn'tunderstand 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 eachweek. 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@postgresql.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 PowerEdgeSC400 using FreeBSD 4.10. Do all of the concepts you relate still apply? I think I did a REINDEX operating a coupleof 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!
peter@vfemail.net writes:
> 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?
Yes, although autovacuum only existed in the form of a contrib module.
You really ought to think about running a newer PG though. The list
of known bugs in 7.4.3 is a mile long, and some of them *will* eat
your data someday.
regards, tom lane