Re: [HACKERS] Re: [COMMITTERS] pgsql: Fix free space map to correctly track the total amount of FSM
> On Tue, Oct 02, 2007 at 09:07:54PM -0400, Alvaro Herrera wrote: > > Decibel! wrote: > > > > > Hrm... what about adding output to vacuum verbose that indicates how many > > > pages in a relation have free space? That would allow something like > > > pgfouine to see how many FSM pages were needed. It would also make it > > > easier to identify relations that could stand a vacuum > > > full/reindex/cluster > > > (though you'd also want to know something like average free space per > > > page). > > > > Rather than wasting time fixing minor FSM issues, I would favor > > rewriting the stuff so that the FSM is disk-spillable. > > Sure, but this would also likely be a 20 line change to vacuum... These proposals would not help, at least me at all. Since I was talking about the pre 8.2 versions. There's 0 chance these changes are backported to previous versions. I'm thinkg about writing a small function which will do something 8.2 or later's vacuum does(telling the right FSM pages needed). -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Re: [COMMITTERS] pgsql: Fix free space map to correctly track the total amount of FSM
On Tue, Oct 02, 2007 at 09:07:54PM -0400, Alvaro Herrera wrote: > Decibel! wrote: > > > Hrm... what about adding output to vacuum verbose that indicates how many > > pages in a relation have free space? That would allow something like > > pgfouine to see how many FSM pages were needed. It would also make it > > easier to identify relations that could stand a vacuum full/reindex/cluster > > (though you'd also want to know something like average free space per > > page). > > Rather than wasting time fixing minor FSM issues, I would favor > rewriting the stuff so that the FSM is disk-spillable. Sure, but this would also likely be a 20 line change to vacuum... -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 pgpJ9N6QD9S1A.pgp Description: PGP signature
Re: [HACKERS] Re: [COMMITTERS] pgsql: Fix free space map to correctly track the total amount of FSM
Decibel! <[EMAIL PROTECTED]> writes: > Hrm... what about adding output to vacuum verbose that indicates how > many pages in a relation have free space? Did you forget the context here? This is 8.1 and before that we're worried about; we're not making such changes in stable releases. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Re: [COMMITTERS] pgsql: Fix free space map to correctly track the total amount of FSM
Decibel! wrote: > Hrm... what about adding output to vacuum verbose that indicates how many > pages in a relation have free space? That would allow something like > pgfouine to see how many FSM pages were needed. It would also make it > easier to identify relations that could stand a vacuum full/reindex/cluster > (though you'd also want to know something like average free space per > page). Rather than wasting time fixing minor FSM issues, I would favor rewriting the stuff so that the FSM is disk-spillable. -- Alvaro Herrera Developer, http://www.PostgreSQL.org/ "Sallah, I said NO camels! That's FIVE camels; can't you count?" (Indiana Jones) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Re: [COMMITTERS] pgsql: Fix free space map to correctly track the total amount of FSM
Dropping -committers. On Oct 2, 2007, at 10:37 AM, Tatsuo Ishii wrote: Tatsuo Ishii <[EMAIL PROTECTED]> writes: Sorry for replying to very old message. But... it seems this was not backported to 8.1 or earlier. Since it involved a change in the FSM API, it didn't seem reasonable to back-patch it. So for those versions of PostgreSQL the only way to know the appropriate FSM pages is change FSM-restart postmaster-do vacuum cycle until vacuum reports the same number of "total page needed"? That's the only easy way I know of, but there is something that might make life easier if you're using autovacuum... take SELECT sum (relpages) FROM pg_class and multiply that by autovacuum_vacuum_scale_factor. If autovac is doing a reasonable job of keeping up, that should be a maximum of what you'd need in the FSM. Hrm... what about adding output to vacuum verbose that indicates how many pages in a relation have free space? That would allow something like pgfouine to see how many FSM pages were needed. It would also make it easier to identify relations that could stand a vacuum full/ reindex/cluster (though you'd also want to know something like average free space per page). -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Re: [COMMITTERS] pgsql: Fix free space map to correctly track the total amount of FSM
> Tatsuo Ishii <[EMAIL PROTECTED]> writes: > > Sorry for replying to very old message. But... it seems this was not > > backported to 8.1 or earlier. > > Since it involved a change in the FSM API, it didn't seem reasonable > to back-patch it. So for those versions of PostgreSQL the only way to know the appropriate FSM pages is change FSM-restart postmaster-do vacuum cycle until vacuum reports the same number of "total page needed"? -- Tatsuo Ishii SRA OSS, Inc. Japan ---(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: [HACKERS] Re: [COMMITTERS] pgsql: Fix free space map to correctly track the total amount of FSM
Tatsuo Ishii <[EMAIL PROTECTED]> writes: > Sorry for replying to very old message. But... it seems this was not > backported to 8.1 or earlier. Since it involved a change in the FSM API, it didn't seem reasonable to back-patch it. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend