Thread: unsubscribe
unsubscribe
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Nelba Sánchez R.
Servicios Tecnológicos de Gestión (STG)
Pontificia Universidad Católica de Chile
fono : (56-2) 686 2316
fax : (56-2) 222 9487
mailto:nnsanche@uc.cl
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?
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).
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?
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.
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"
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
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.
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