Обсуждение: 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.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