Thread: When to vacuum a table?

Hi,

Are there guidelines (or any empirical data) available how to determine
how often a table should be vacuumed for optimum performance or is this
an experience / trial-and-error thing?

TIA

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl

Hi,

From: http://www.postgresql.org/docs/7.4/interactive/sql-vacuum.html


"VACUUM reclaims storage occupied by deleted tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables."

"The "vacuum analyze" form additionally collects statistics on the disbursion of columns in the database, which the optimizer uses when it calculates just how to execute queries. The availability of this data can make a tremendous difference in the execution speed of queries. This command can also be run from cron, but it probably makes more sense to run this command as part of your nightly backup procedure - if "vacuum" is going to screw up the database, you'd prefer it to happen immediately after (not before!) you've made a backup! The "vacuum" command is very reliable, but conservatism is the key to good system management. So, if you're using the export procedure described above, you don't need to do this extra step".

All its tables constantly manipulated (INSERT, UPDATE, DELETE) they need a VACUUM, therefore the necessity to execute at least one time to the day normally of dawn if its database will be very great .

[],s

Marcelo Costa
Secretaria Executiva de Educação do Pará
Amazonia - Pará - Brazil
 



--
Marcelo Costa

On Sun, Nov 26, 2006 at 09:43:11AM -0300, Marcelo Costa wrote:
> All its tables constantly manipulated (INSERT, UPDATE, DELETE) they need a
> VACUUM

Just a minor clarification here: INSERT does not create dead rows, only
UPDATE and DELETE do. Thus, if you only insert rows, you do not need to
vacuum (although you probably need to analyze).

/* Steinar */
--
Homepage: http://www.sesse.net/

Sorry,

realy you are correct.

[],s

Marcelo Costa.




--
Marcelo Costa

On 26-Nov-06, at 8:11 AM, Steinar H. Gunderson wrote:

> On Sun, Nov 26, 2006 at 09:43:11AM -0300, Marcelo Costa wrote:
>> All its tables constantly manipulated (INSERT, UPDATE, DELETE)
>> they need a
>> VACUUM
>
> Just a minor clarification here: INSERT does not create dead rows,
> only
> UPDATE and DELETE do. Thus, if you only insert rows, you do not
> need to
> vacuum (although you probably need to analyze).

Not entirely true. An insert & rollback will create dead rows. If you
attempt and fail a large number of insert transactions then you will
still need to vacuum.

On Sun, Nov 26, 2006 at 09:24:29AM -0500, Rod Taylor wrote:
> attempt and fail a large number of insert transactions then you will
> still need to vacuum.

And you still need to vacuum an insert-only table sometimes, because
of the system-wide vacuum requirement.

A


--
Andrew Sullivan  | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
        --George Orwell

Rod Taylor wrote:
>> Just a minor clarification here: INSERT does not create dead rows, only
>> UPDATE and DELETE do. Thus, if you only insert rows, you do not need to
>> vacuum (although you probably need to analyze).

Is there no real-time garbage collection at all in Postgres?  And if so, is this because nobody has had time to
implementgarbage collection, or for a more fundamental reason, or because VACUUM is seen as sufficient? 

I'm just curious ... Vacuum has always seemed to me like an ugly wart on the pretty face of Postgres.  (I say this even
thoughI implemented an identical solution on a non-relational chemistry database system a long time ago.  I didn't like
itthen, either.)  

Craig

"Craig A. James" <cjames@modgraph-usa.com> writes:
> Is there no real-time garbage collection at all in Postgres?

No.

> And if so, is this because nobody has had time to implement garbage
> collection, or for a more fundamental reason, or because VACUUM is
> seen as sufficient?

If you really want to know, read the mountains of (mostly) junk that
have been written about replacing VACUUM in pgsql-hackers.  The short
answer (with apologies to Winston Churchill) is that VACUUM is the worst
solution, except for all the others that have been suggested.

            regards, tom lane

> If you really want to know, read the mountains of (mostly) junk that
> have been written about replacing VACUUM in pgsql-hackers.  The short
> answer (with apologies to Winston Churchill) is that VACUUM is the worst
> solution, except for all the others that have been suggested.

The lesser of 50 evils? ;)

Joshua D. Drake



>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

On Sun, Nov 26, 2006 at 12:24:17PM +0100, Joost Kraaijeveld wrote:
> Hi,
>
> Are there guidelines (or any empirical data) available how to determine
> how often a table should be vacuumed for optimum performance or is this
> an experience / trial-and-error thing?

Most of the time I just turn autovac on, set the scale factors to
0.2/0.1 and the thresholds to 300/200 and turn on vacuum_cost_delay
(usually set to 20). That's a pretty decent setup for most applications.
It also doesn't hurt to run a periodic vacuumdb -av and look at the tail
end of it's output to make sure you have adequate FSM settings.

The exception to that rule is for tables that are very small and have a
lot of churn; I'll vacuum those by hand very frequently (every 60
seconds or better).
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

>>> On Sun, Nov 26, 2006 at  5:24 AM, in message
<1164540257.7902.0.camel@panoramix>, Joost Kraaijeveld
<J.Kraaijeveld@Askesis.nl> wrote:
>
> Are there guidelines (or any empirical data) available how to
determine
> how often a table should be vacuumed for optimum performance or is
this
> an experience / trial- and- error thing?

For most of our databases we use a daily "VACUUM ANALYZE VERBOSE;".  We
grep the results to show large numbers of removable  or dead rows and to
show the fsm numbers at the end.  There are a few small tables with high
update rates which this doesn't cover.  To handle that we set the
autovacuum to 0.2/0.1 and 1/1 on a ten second interval and do a daily
CLUSTER of these tables.  We really don't have many tables which can hit
these autovacuum thresholds in one day.

We have databases which are 400 GB with the vast majority of that being
in tables which are insert-only except for a weekly purge of data over a
year old.  We do nightly vacuums on the few tables with update/delete
activity, and a weekly vacuum of the whole database -- right after the
delete of old rows from the big tables.

-Kevin