Thread: max_fsm_pages

max_fsm_pages

From

"Jouneau Luc"

Date:

04 September 2003, 17:56:01

Hi everybody,

I'm trying to understand how vacuumdb and free space map work together for retrieving free spaces after delete or update statements.

max_fsm_pages and max_fsm_relations are set to default values (respectively 10000 and 100).

I do my tests on a table containing at the beginning 1931968 tuples stocked in 11944 pages :

INFO: Pages 11944: Changed 11944, Empty 0; Tup 1931968: Vac 0, Keep 0, UnUsed 0

I delete 1931936 tuples (letting 32 tuples left).

I thought the number of pages affected by this delete will overflow fsm storage capacity, but when I run a vacuumdb verbose analyze on this table,

I have the following log information :

INFO: Removed 1397914 tuples in 8641 pages.

CPU 0.76s/0.89u sec elapsed 25.72 sec.

INFO: Removed 534022 tuples in 3303 pages.

CPU 0.37s/0.29u sec elapsed 7.62 sec.

INFO: Pages 11944: Changed 11944, Empty 0; Tup 32: Vac 1931936, Keep 0, UnUsed 0.

Total CPU 1.57s/1.90u sec elapsed 42.01 sec.

It seems that FSM traced all of my deletes since it mentions removes in 8641+3303=11944 pages (>10000 which was set for max_fsm_page).

Can someone explain me, where I'm wrong ?

My first aim was to know if max_fsm_pages was set for each table or for a whole database. Per table I suppose, but Is it a good supposition ?

Where is stored (on disk) this fsm ?

Thanks for your answers.

Luc

Re: max_fsm_pages

From

Tom Lane

Date:

04 September 2003, 23:24:20

"Jouneau Luc" <Luc.Jouneau@jouy.inra.fr> writes:
> INFO:  Pages 11944: Changed 11944, Empty 0; Tup 32: Vac 1931936, Keep 0, Un=
> Used 0.
>         Total CPU 1.57s/1.90u sec elapsed 42.01 sec.

> It seems that FSM traced all of my deletes since it mentions removes in 864=
> 1+3303=3D11944 pages (>10000 which was set for max_fsm_page).

The numbers output by VACUUM don't tell you anything about how many of
those pages will actually be remembered in the FSM.

In practice, max_fsm_relations=100 is too small; we've bumped the
default to 1000 for 7.4.

> My first aim was to know if max_fsm_pages was set for each table or for a w=
> hole database.

Whole database.  That default setting is very small, too (enough for
a database of maybe a couple hundred meg, at most).

            regards, tom lane