max_fsm_pages

[Ryan Mahoney]

When interactively calculating the ideal value for max_fsm_pages by
summarizing the output of VACUUM VERBOSE, which statistic from vacuum am
I concerned with?

We've got a database that takes about 5 hours to VACUUM FULL and
prevents the server from doing anything useful during that time.
Currently it is scheduled to run twice weekly, but I would like to start
using the auto vacuum daemon instead.

Thanks!

-r
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

Ryan Mahoney <ry**@paymentalliance.net> writes:

When interactively calculating the ideal value for max_fsm_pages by
summarizing the output of VACUUM VERBOSE, which statistic from vacuum am
I concerned with?

7.4 will tell you exactly how many FSM slots you need:

foo=# vacuum verbose;
....
INFO: free space map: 246 relations, 464 pages stored; 4160 total pages needed
DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 178 kB shared memory.
VACUUM

In this example, the minimum FSM settings to not discard any data would
be max_fsm_relations = 246, max_fsm_pages = 4160. Note that you need to
have vacuumed all databases fairly recently for the totals to be really
trustworthy.

In previous versions you're kind of on your own :-(

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Ryan Mahoney]

> In previous versions you're kind of on your own :-(

Hmmm, in this case we need to use 7.3.2 because is it the only supported
postgres version from the application vendor. In 7.3.2, Vacuum tells
me:

INFO: Pages 248: Changed 0, reaped 244, Empty 0, New 0; Tup 4464: Vac
0, Keep/VTL 0/0, UnUsed 1644, MinLen 184, MaxLen 571; Re-using:
Free/Avail. Space 33436/19528; EndEmpty/Avail. Pages 0/47

for each table. Can is sum one of those values across the databse - or
are they not really relevant to max_fsm_pages?

Thanks!

-r
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Franco Bruno Borghesi]

I've read this message, and tryed to apply this changes to my own
database.

sapiens=# vacuum verbose;
....
INFO: free space map: 1 relations, 39 pages stored; 48 total pages
needed
DETAIL: Allocated FSM size: 1000 relations + 30000 pages = 237 kB
shared memory.

1 relation and 48 pages seems too little, considering the defaults in
the configuration.

The database is not too big, just 198 mb (contrib/dbsize tells me so),
and I have 161 relations on pg_class (not counting the catalog).

I'm doing something wrong or 1 relation/48 pages would be just fine?

Thanks.

On Fri, 2003-12-05 at 13:17, Tom Lane wrote:

Ryan Mahoney <ry**@paymentalliance.net> writes:

When interactively calculating the ideal value for max_fsm_pages by
summarizing the output of VACUUM VERBOSE, which statistic from vacuum am
I concerned with?

7.4 will tell you exactly how many FSM slots you need:

foo=# vacuum verbose;
...
INFO: free space map: 246 relations, 464 pages stored; 4160 total pages needed
DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 178 kB shared memory.
VACUUM

In this example, the minimum FSM settings to not discard any data would
be max_fsm_relations = 246, max_fsm_pages = 4160. Note that you needto
have vacuumed all databases fairly recently for the totals to be really
trustworthy.

In previous versions you're kind of on your own :-(

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQA/1KN221dVnhLsBV0RAjLtAJwJxWUK8NDHpSXuFS6oKEObRcgv4Q CfdGuh
2dHz0eb6O/eedHxpBX6q10o=
=sNZW
-----END PGP SIGNATURE-----

[Tom Lane]

Franco Bruno Borghesi <fr****@akyasociados.com.ar> writes:

sapiens=3D# vacuum verbose;
...
INFO: free space map: 1 relations, 39 pages stored; 48 total pages
needed

Apparently this is a freshly loaded database that you've not made many
changes in yet?

I'd recommend waiting till you've had some level of normal update
activity before trying to discover an optimal size for FSM ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org