473,326 Members | 2,126 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #1
4 8258
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

Nov 12 '05 #2
> 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

Nov 12 '05 #3
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-----

Nov 12 '05 #4
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

Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jeff Boes | last post by:
What might be the source of this error? Cache lookup failed for relation 188485009 We've been getting these at odd intervals, and they are not reproducible. Our setup: PostgreSQL 7.3.3...
236
by: Andrew Rawnsley | last post by:
Anyone out there using beta 2 in production situations? Comments on stability? I am rolling out a project in the next 4 weeks, and really don't want to go though an upgrade soon after its released...
10
by: Josué Maldonado | last post by:
Hello list, I have 7.3.4 on RH 8, server hardware is a dual processor Intel Xeon 2.4 Ghz, 2G RAM. I was reading about tunning and would like to get some help from you, I changed some of the...
6
by: Gregory Wood | last post by:
Hello all, I sure hope someone can help me out as I'm having the worst time trying to get this upgraded server running. I just upgraded to 7.4.1 (running on FreeBSD, upgraded through the ports...
5
by: Clodoaldo Pinto Neto | last post by:
I suspect there is something wrong because it takes 73s to delete a single line from a table whith 140 lines. So I tried to reindex the database: bash-2.05b$ postgres -P -O -D /var/lib/pgsql/data...
15
by: Ed L. | last post by:
If I see VACUUM ANALYZE VERBOSE output like this... INFO: --Relation public.foo-- INFO: Index idx_foo_bar: Pages 219213; Tuples 28007: Deleted 9434. CPU 17.05s/4.58u sec elapsed 3227.62 sec. ...
4
by: Ed L. | last post by:
I am trying to better understand diskspace leakage and the relationship to vacuum, max_fsm_pages, and max_fsm_relations. Below are 3 snippets from 3 successive vacuums on a table with ~284K rows...
6
by: wespvp | last post by:
If max_fsm_pages is too small and I have space not reclaimed by vacuum, if I increase max_fsm_pages and restart postmaster, will the next VACUUM ANALYZE relcaim all overlooked pages or must I do a...
0
by: Thomas F.O'Connell | last post by:
Matthew, Here's some more feedback on our use of pg_autovaccum. It's clear that it's working and that it's helping, but even after increasing our max_fsm_pages substantially (to in excess of...
2
by: Ulrich Wisser | last post by:
Hi, lately I've been acused of "stone-age maintenance procedures". Hopefully I will find some help to develop from stone-age to roman empire. ;-) To optimize the performance and to minimize...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.