Re: [PERFORM] VACUUM FULL vs CLUSTER
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 + 7 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 ---(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
Re: [PERFORM] VACUUM FULL vs CLUSTER
On Tue, Sep 20, 2005 at 14:53:19 -0400, Markus Benne [EMAIL PROTECTED] 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). ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
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). What version of postgres was the last version that had the index bloat problem? ---(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
Re: [PERFORM] VACUUM FULL vs CLUSTER
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] VACUUM FULL vs CLUSTER
On Fri, Sep 23, 2005 at 18:16:44 +0200, Stef [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
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
Re: [PERFORM] VACUUM FULL vs CLUSTER
[EMAIL PROTECTED] (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 [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] VACUUM FULL vs CLUSTER
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? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
