pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVACUUM FULL vs CLUSTER
8+ messages / 6 participants
[nested] [flat]
* VACUUM FULL vs CLUSTER
@ 2005-09-20 18:53 Markus Benne <markus@m-bass.com>
0 siblings, 1 reply; 8+ messages in thread
From: Markus Benne @ 2005-09-20 18:53 UTC (permalink / raw)
To: pgsql-performance
I have a table that is purged by 25% each night. I'd like to do a
vacuum nightly after the purge to reclaim the space, but I think I'll
still need to do a vacuum full weekly.
Would there be any benefit to doing a cluster instead of the vacuum?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: VACUUM FULL vs CLUSTER
@ 2005-09-23 14:20 Bruno Wolff III <bruno@wolff.to>
parent: Markus Benne <markus@m-bass.com>
0 siblings, 1 reply; 8+ messages in thread
From: Bruno Wolff III @ 2005-09-23 14:20 UTC (permalink / raw)
To: Markus Benne <markus@m-bass.com>; +Cc: pgsql-performance
On Tue, Sep 20, 2005 at 14:53:19 -0400,
Markus Benne <markus@m-bass.com> wrote:
> I have a table that is purged by 25% each night. I'd like to do a
> vacuum nightly after the purge to reclaim the space, but I think I'll
> still need to do a vacuum full weekly.
>
> Would there be any benefit to doing a cluster instead of the vacuum?
If you have a proper FSM setting you shouldn't need to do vacuum fulls
(unless you have an older version of postgres where index bloat might
be an issue).
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: VACUUM FULL vs CLUSTER
@ 2005-09-23 16:16 Stef <svb@ucs.co.za>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 3 replies; 8+ messages in thread
From: Stef @ 2005-09-23 16:16 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Markus Benne <markus@m-bass.com>; pgsql-performance
Bruno Wolff III mentioned :
=> If you have a proper FSM setting you shouldn't need to do vacuum fulls
=> (unless you have an older version of postgres where index bloat might
=> be an issue).
What version of postgres was the last version that had
the index bloat problem?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: VACUUM FULL vs CLUSTER
@ 2005-09-23 16:59 Bruno Wolff III <bruno@wolff.to>
parent: Stef <svb@ucs.co.za>
2 siblings, 1 reply; 8+ messages in thread
From: Bruno Wolff III @ 2005-09-23 16:59 UTC (permalink / raw)
To: Stef <svb@ucs.co.za>; +Cc: Markus Benne <markus@m-bass.com>; pgsql-performance
On Fri, Sep 23, 2005 at 18:16:44 +0200,
Stef <svb@ucs.co.za> wrote:
> Bruno Wolff III mentioned :
> => If you have a proper FSM setting you shouldn't need to do vacuum fulls
> => (unless you have an older version of postgres where index bloat might
> => be an issue).
>
> What version of postgres was the last version that had
> the index bloat problem?
You can check the release notes to be sure, but my memory is that the
unbounded bloat problem was fixed in 7.4. There still are usage patterns
that can result in bloating, but it is limited to some constant multiplier
of the minimum index size.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: VACUUM FULL vs CLUSTER
@ 2005-09-23 17:03 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Stef <svb@ucs.co.za>
2 siblings, 0 replies; 8+ messages in thread
From: Alvaro Herrera @ 2005-09-23 17:03 UTC (permalink / raw)
To: Stef <svb@ucs.co.za>; +Cc: Bruno Wolff III <bruno@wolff.to>; Markus Benne <markus@m-bass.com>; pgsql-performance
On Fri, Sep 23, 2005 at 06:16:44PM +0200, Stef wrote:
> Bruno Wolff III mentioned :
> => If you have a proper FSM setting you shouldn't need to do vacuum fulls
> => (unless you have an older version of postgres where index bloat might
> => be an issue).
>
> What version of postgres was the last version that had
> the index bloat problem?
The worst problems were solved in 7.4. There are problems in certain
limited circumstances even with current releases.
--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34
"The ability to monopolize a planet is insignificant
next to the power of the source"
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: VACUUM FULL vs CLUSTER
@ 2005-09-23 17:18 Stef <svb@ucs.co.za>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 8+ messages in thread
From: Stef @ 2005-09-23 17:18 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Markus Benne <markus@m-bass.com>; pgsql-performance
Bruno Wolff III mentioned :
=> > => If you have a proper FSM setting you shouldn't need to do vacuum fulls
=> > => (unless you have an older version of postgres where index bloat might
=> > => be an issue).
Thanks Alvaro and Bruno
I just want to clarify something that I also couldn't
find a clear cut answer for before.
What is a proper fsm setting?
Someone told me to set max_fsm_relations to the number of
relations in pg_class plus a few more to allow for new relations.
And max_fsm_pages to the number of rows in the biggest table I
want to vacuum, plus a few 1000's for extra room?
Where does this free space map sit? On the disk somewhere,
or in memory, or both.
I once set the max_fsm_pages very high by mistake, and postgres
then started up and used a _lot_ of shared memory, and I had to
increase shmmax. Is there abything to watch out for when bumping this
setting up a lot?
Kind Regards
Stefan
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: VACUUM FULL vs CLUSTER
@ 2005-09-23 17:48 Chris Browne <cbbrowne@acm.org>
parent: Stef <svb@ucs.co.za>
2 siblings, 0 replies; 8+ messages in thread
From: Chris Browne @ 2005-09-23 17:48 UTC (permalink / raw)
To: pgsql-performance
svb@ucs.co.za (Stef) writes:
> Bruno Wolff III mentioned :
> => If you have a proper FSM setting you shouldn't need to do vacuum fulls
> => (unless you have an older version of postgres where index bloat might
> => be an issue).
>
> What version of postgres was the last version that had
> the index bloat problem?
I believe that was fixed in 7.3; it was certainly resolved by 7.4...
--
(format nil "~S@~S" "cbbrowne" "cbbrowne.com")
http://www.ntlug.org/~cbbrowne/spiritual.html
MICROS~1 has brought the microcomputer OS to the point where it is
more bloated than even OSes from what was previously larger classes of
machines altogether. This is perhaps Bill's single greatest
accomplishment.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: VACUUM FULL vs CLUSTER
@ 2005-09-27 16:21 Dario <dario_d_s@unitech.com.ar>
parent: Stef <svb@ucs.co.za>
0 siblings, 0 replies; 8+ messages in thread
From: Dario @ 2005-09-27 16:21 UTC (permalink / raw)
To: Stef <svb@ucs.co.za>; Bruno Wolff III <bruno@wolff.to>; +Cc: Markus Benne <markus@m-bass.com>; pgsql-performance
you can see that at the end of vacuum log (sorry for my english)
...
INFO: free space map: 930 relations, 48827 pages stored; 60240 total pages
needed -- NEEDED!
-- I have already configured in postgresql.conf, you can see it below
DETAIL: Allocated FSM size: 1000 relations + 70000 pages = 475 kB shared
memory. -- ALLOCATED ACCORDING TO max_fsm_pages , etc
VACUUM
You probably must adjust your shared memory, coz the database need it, but
it depends on your database...
(I could be wrong, I'm learning postgresql, please, feel free to correct me)
-----Mensaje original-----
De: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]En nombre de Stef
Enviado el: viernes, 23 de septiembre de 2005 14:18
Para: Bruno Wolff III
CC: Markus Benne; pgsql-performance@postgresql.org
Asunto: Re: [PERFORM] VACUUM FULL vs CLUSTER
Bruno Wolff III mentioned :
=> > => If you have a proper FSM setting you shouldn't need to do vacuum
fulls
=> > => (unless you have an older version of postgres where index bloat
might
=> > => be an issue).
Thanks Alvaro and Bruno
I just want to clarify something that I also couldn't
find a clear cut answer for before.
What is a proper fsm setting?
Someone told me to set max_fsm_relations to the number of
relations in pg_class plus a few more to allow for new relations.
And max_fsm_pages to the number of rows in the biggest table I
want to vacuum, plus a few 1000's for extra room?
Where does this free space map sit? On the disk somewhere,
or in memory, or both.
I once set the max_fsm_pages very high by mistake, and postgres
then started up and used a _lot_ of shared memory, and I had to
increase shmmax. Is there abything to watch out for when bumping this
setting up a lot?
Kind Regards
Stefan
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2005-09-27 16:21 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-20 18:53 VACUUM FULL vs CLUSTER Markus Benne <markus@m-bass.com>
2005-09-23 14:20 ` Bruno Wolff III <bruno@wolff.to>
2005-09-23 16:16 ` Stef <svb@ucs.co.za>
2005-09-23 16:59 ` Bruno Wolff III <bruno@wolff.to>
2005-09-23 17:18 ` Stef <svb@ucs.co.za>
2005-09-27 16:21 ` Dario <dario_d_s@unitech.com.ar>
2005-09-23 17:03 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-09-23 17:48 ` Chris Browne <cbbrowne@acm.org>
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