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
[HACKERS] Re: [COMMITTERS] pgsql: Fix free space map to correctly track the total amount of FSM
Sorry for replying to very old message. But... it seems this was not backported to 8.1 or earlier. If so, how one could determine max_fsm_pages is sufficient or not if he is running 8.1 or earlier? -- Tatsuo Ishii SRA OSS, Inc. Japan Log Message: --- Fix free space map to correctly track the total amount of FSM space needed even when a single relation requires more than max_fsm_pages pages. Also, make VACUUM emit a warning in this case, since it likely means that VACUUM FULL or other drastic corrective measure is needed. Per reports from Jeff Frost and others of unexpected changes in the claimed max_fsm_pages need. Modified Files: -- pgsql/contrib/pg_freespacemap: README.pg_freespacemap (r1.4 - r1.5) (http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_freespacemap/README.pg_freespacemap.diff?r1=1.4r2=1.5) pg_freespacemap.c (r1.6 - r1.7) (http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_freespacemap/pg_freespacemap.c.diff?r1=1.6r2=1.7) pg_freespacemap.sql.in (r1.5 - r1.6) (http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_freespacemap/pg_freespacemap.sql.in.diff?r1=1.5r2=1.6) pgsql/src/backend/access/gin: ginvacuum.c (r1.5 - r1.6) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/gin/ginvacuum.c.diff?r1=1.5r2=1.6) pgsql/src/backend/access/gist: gistvacuum.c (r1.26 - r1.27) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/gist/gistvacuum.c.diff?r1=1.26r2=1.27) pgsql/src/backend/access/nbtree: nbtree.c (r1.150 - r1.151) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/nbtree/nbtree.c.diff?r1=1.150r2=1.151) pgsql/src/backend/commands: vacuum.c (r1.339 - r1.340) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/vacuum.c.diff?r1=1.339r2=1.340) vacuumlazy.c (r1.78 - r1.79) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/vacuumlazy.c.diff?r1=1.78r2=1.79) pgsql/src/backend/storage/freespace: freespace.c (r1.54 - r1.55) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/storage/freespace/freespace.c.diff?r1=1.54r2=1.55) pgsql/src/include/storage: freespace.h (r1.21 - r1.22) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/storage/freespace.h.diff?r1=1.21r2=1.22) ---(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 ---(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
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
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
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
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
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
