Обсуждение: max_fsm_pages

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

Ryan Mahoney <ryan@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

> 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

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 <ryan@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 majordomo@postgresql.org so that your     message can get through to the mailing list cleanly

Franco Bruno Borghesi <franco@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