Thread: 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

> 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