Re: Vacuuming -- again

On Thu, Jan 20, 2011 at 2:34 PM, <peter(at)vfemail(dot)net> wrote:
>
> 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.
>
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>