pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhen to vacuum a table?
11+ messages / 10 participants
[nested] [flat]
* When to vacuum a table?
@ 2006-11-26 11:24 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 3 replies; 11+ messages in thread
From: Joost Kraaijeveld @ 2006-11-26 11:24 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: When to vacuum a table?
@ 2006-11-26 12:43 Marcelo Costa <marcelojscosta@gmail.com>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2 siblings, 1 reply; 11+ messages in thread
From: Marcelo Costa @ 2006-11-26 12:43 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>; +Cc: pgsql-performance
Hi,
From: http://www.postgresql.org/docs/7.4/interactive/sql-vacuum.html
"VACUUM reclaims storage occupied by deleted tuples. In normal
PostgreSQLoperation, 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
2006/11/26, Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>:
>
> 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
>
> ---------------------------(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
>
--
Marcelo Costa
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: When to vacuum a table?
@ 2006-11-26 13:11 Steinar H. Gunderson <sgunderson@bigfoot.com>
parent: Marcelo Costa <marcelojscosta@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Steinar H. Gunderson @ 2006-11-26 13:11 UTC (permalink / raw)
To: pgsql-performance
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/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: When to vacuum a table?
@ 2006-11-26 13:46 Marcelo Costa <marcelojscosta@gmail.com>
parent: Steinar H. Gunderson <sgunderson@bigfoot.com>
1 sibling, 0 replies; 11+ messages in thread
From: Marcelo Costa @ 2006-11-26 13:46 UTC (permalink / raw)
To: pgsql-performance
Sorry,
realy you are correct.
[],s
Marcelo Costa.
2006/11/26, Steinar H. Gunderson <sgunderson@bigfoot.com>:
>
> 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/
>
> ---------------------------(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
>
--
Marcelo Costa
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: When to vacuum a table?
@ 2006-11-26 14:24 Rod Taylor <rod.taylor@gmail.com>
parent: Steinar H. Gunderson <sgunderson@bigfoot.com>
1 sibling, 2 replies; 11+ messages in thread
From: Rod Taylor @ 2006-11-26 14:24 UTC (permalink / raw)
To: Steinar H. Gunderson <sgunderson@bigfoot.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: When to vacuum a table?
@ 2006-11-26 14:33 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Rod Taylor <rod.taylor@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Andrew Sullivan @ 2006-11-26 14:33 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: When to vacuum a table?
@ 2006-11-26 16:26 Craig A. James <cjames@modgraph-usa.com>
parent: Rod Taylor <rod.taylor@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Craig A. James @ 2006-11-26 16:26 UTC (permalink / raw)
To: pgsql-performance
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 implement garbage 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 though I implemented an identical solution on a non-relational chemistry database system a long time ago. I didn't like it then, either.)
Craig
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: When to vacuum a table?
@ 2006-11-26 17:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Craig A. James <cjames@modgraph-usa.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2006-11-26 17:06 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: When to vacuum a table?
@ 2006-11-26 17:47 Joshua D. Drake <jd@commandprompt.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: Joshua D. Drake @ 2006-11-26 17:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Craig A. James <cjames@modgraph-usa.com>; pgsql-performance
> 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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: When to vacuum a table?
@ 2006-11-27 01:01 Jim C. Nasby <jim@nasby.net>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2 siblings, 0 replies; 11+ messages in thread
From: Jim C. Nasby @ 2006-11-27 01:01 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: pgsql-performance
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)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: When to vacuum a table?
@ 2006-11-27 18:59 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2 siblings, 0 replies; 11+ messages in thread
From: Kevin Grittner @ 2006-11-27 18:59 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; pgsql-performance
>>> On Sun, Nov 26, 2006 at 5:24 AM, in message
<camel@panoramix/">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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2006-11-27 18:59 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-26 11:24 When to vacuum a table? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2006-11-26 12:43 ` Marcelo Costa <marcelojscosta@gmail.com>
2006-11-26 13:11 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-11-26 13:46 ` Marcelo Costa <marcelojscosta@gmail.com>
2006-11-26 14:24 ` Rod Taylor <rod.taylor@gmail.com>
2006-11-26 14:33 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-11-26 16:26 ` Craig A. James <cjames@modgraph-usa.com>
2006-11-26 17:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-26 17:47 ` Joshua D. Drake <jd@commandprompt.com>
2006-11-27 01:01 ` Jim C. Nasby <jim@nasby.net>
2006-11-27 18:59 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox