Re: Vacuuming -- again - Mailing list pgsql-novice
From | Michael Swierczek |
---|---|
Subject | Re: Vacuuming -- again |
Date | |
Msg-id | AANLkTi=n684KS888=byow4i_Z__-FEH9yNyOANtBDeYA@mail.gmail.com Whole thread Raw |
In response to | Vacuuming -- again (peter@vfemail.net) |
List | pgsql-novice |
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 >
pgsql-novice by date: