Thread: Re: [COMMITTERS] pgsql: Fix free space map to correctly track the total amount of FSM
Re: [COMMITTERS] pgsql: Fix free space map to correctly track the total amount of FSM
From
Tatsuo Ishii
Date:
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.4&r2=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.6&r2=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.5&r2=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.5&r2=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.26&r2=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.150&r2=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.339&r2=1.340) > vacuumlazy.c (r1.78 -> r1.79) > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/vacuumlazy.c.diff?r1=1.78&r2=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.54&r2=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.21&r2=1.22) > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Re: Re: [COMMITTERS] pgsql: Fix free space map to correctly track the total amount of FSM
From
Tom Lane
Date:
Tatsuo Ishii <ishii@postgresql.org> 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
Re: Re: [COMMITTERS] pgsql: Fix free space map to correctly track the total amount of FSM
From
Tatsuo Ishii
Date:
> Tatsuo Ishii <ishii@postgresql.org> 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
Re: Re: [COMMITTERS] pgsql: Fix free space map to correctly track the total amount of FSM
From
Decibel!
Date:
Dropping -committers. On Oct 2, 2007, at 10:37 AM, Tatsuo Ishii wrote: >> Tatsuo Ishii <ishii@postgresql.org> 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 decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
Re: Re: [COMMITTERS] pgsql: Fix free space map to correctly track the total amount of FSM
From
Alvaro Herrera
Date:
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)
Re: Re: [COMMITTERS] pgsql: Fix free space map to correctly track the total amount of FSM
From
Tom Lane
Date:
Decibel! <decibel@decibel.org> 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
Re: Re: [COMMITTERS] pgsql: Fix free space map to correctly track the total amount of FSM
From
Decibel!
Date:
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 decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
Re: Re: [COMMITTERS] pgsql: Fix free space map to correctly track the total amount of FSM
From
Tatsuo Ishii
Date:
> 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