pgsql: Fix free space map to correctly track the total amount of FSM
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)
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
Show quoted text
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
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
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
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
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)
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
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
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